Re: DB2: Generating epoch time

On 13-Nov-2015 12:19 -0600, CRPence wrote:

On 12-Nov-2015 10:14 -0600, rob wrote:

<<SNIP>>
[From HEX(GENERATE_UNIQUE())] I get
02144325069C758C0551A07001

<<SNIP>> that [epoch_time] UDF) is returning an epoch of
1447343795

Due to slight run time differences I can expect some variance but
not 14432... vs 14473...
<<SNIP>>

Quite likely, those first several _hexadecimal digits_ have nothing
to do with the /date and time/, and instead have something to do
with the /place/; i.e. representative of the /space/ portion of an
_effective_ UUID that is produced by the SQL HEX(GENERATE_UNIQUE())
<<SNIP>>

FWiW, the following seem to reveal what are 13 hex digits that are representing 52 bits of precision used to store the timestamp within the GENERATE_UNIQUE() data; the following example uses the hex data from the GENERATE_UNIQUE() shown in the earliest quoted message, and for which all of the seemingly non-timestamp data was replaced by the hex digit 'F' to make that more conspicuous:


with t ( t1 , t0 ) as/* TS precision:xxxxxxxxxxxxx: 52 bits */
(select timestamp( cast( x'FFFFFFFFFF9C758C0551A07FFF'
as char(13) for bit data ) )
, timestamp( cast( x'FFFFFFFFFF0000000000000FFF'
as char(13) for bit data ) )
from qsqptabl
)
select
char(date(t1), iso) t1 /* shorten timestamp to date */
, char(date(t0), iso) t0 /* shorten timestamp to date */
/* subtract recent date from /zero-date/ for duration:*/
, char(date(timestamp( /* re-represent duration as date */
digits( dec( cast( t1 - t0 as char(22) ), 14 ) )
)), ISO) td
from t
-- effect from the above query:
....+....1....+....2....+....3....
T1 T0 TD
2015-11-12 1928-08-23 0087-02-20
******** End of data ********


Given the value of zero gives the 23-Aug-1928 date shown above [and that a separate test of all 13 digits as F gives the date 10-May-2071], the similarity to a *DTS is striking.