On 10-Nov-2015 15:08 -0600, rob wrote:
Is there some way to generate the epoch time in SQL?

The following might be of assistance, though other replies might have already given what is the desired effect.

Nothing to do with epoch, just some comments about the attempts made with the GENERATE_UNIQUE SQL scalar:

I tried
values generate_unique()
but that was blank.
And I tried the ancient
select generate_unique() from sysibm.sysdummy1
but that worked the same

Probably not /blank/, rather, what was attempted to be displayed by the report writer were likely _mostly_ non-displayable characters. The output from the GENERATE_UNIQUE scalar is essentially of BINARY data type; actually matches the FOR BIT DATA. Typically the output from the function is only viewed as a character string showing the hexadecimal representation or the timestamp; i.e. one of:
hex(generate_unique()) or timestamp(generate_unique())

I took a wag at
values cast(generate_unique() as char(30))
but that wasn't any better.

And te effect of casting the effective binary data to a character data type with encoding is no different for the 5250 report writer; the Query/400 report writer used for non-DRDA SELECT queries in STRSQL will try to put the data to the display, and if the WS returns errors, the data will be translated converting all non-displayable characters to blanks; though the expected effect if the error occurred from the WS, would be a QRY1041 "Cannot show this data at the display station." in the message-line to indicate the effect.

This was a little different
values generate_unique(), timestamp(generate_unique())
******** End of data ***

Before I never even got the plus signs.

The joblog would show what was the failed mapping; if not run in debug, then possibly only visible in the active joblog while the report panel /Display Data/ is still active.

Because the two different data types are requested for one column, the SQL would attempt to choose a compatible type betwixt [IIRC, using data type _promotion_ rules, just like for an explicit UNION of those two scalar results]; in this case, apparently the TIMESTAMP was deemed the data type for the result-column. But given the first value was effective gibberish, as a character string to represent a TIMESTAMP value, the character-13 value was unable to be converted into a timestamp value and thus effected a /data mapping error/ [which in the Query/400 report writer is manifest by all plus symbols].

I am not sure why however, in any release, that a CHAR(13) FOR BIT DATA would be considered /compatible/ rather than the effecting an error; even in 7.2 the minimum is 14-bytes. I am just pointing out what the effects appear to have been [given the report shown for the query request shown], not to imply that is the proper effect; that the query even produced output, rather than issuing an error preventing the query even starting, may be a defect.


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].