Re: DB2: Generating epoch time

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.
[https://www.google.com/search?q=epoch+site%3Aarchive.midrange.com]

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())
....+....1....+....2....+.
VALUES
++++++++++++++++++++++++++
2015-11-10-21.04.02.786923
******** 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.

<<SNIP>>