Epoch

Note to self because it’s just one of those date/timezone-related topics which just doesn’t seem to stick…

Epoch/Unix time – See https://en.wikipedia.org/wiki/Unix_time

Unix time (also known as POSIX time or erroneously as Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,

Firstly when converting from Oracle date or timestamp – we need to work from UTC not local time.

select systimestamp
,      ((extract( day    from systimestamp - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*24*60*60)
      + (extract( hour   from systimestamp - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60*60)
      + (extract( minute from systimestamp - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60)
      + (round(extract( second from systimestamp - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY')))))*1000
        now_epoch
from dual;

SYSTIMESTAMP                         NOW_EPOCH
----------------------------------- -------------
26-JUN-15 11.57.09.634813000 +01:00 1435319830000 

If we plug that epoch into somewhere like http://www.epochconverter.com/ , we can see it’s wrong – it’s 1 hour ahead of where it should be:

Assuming that this timestamp is in milliseconds:
GMT: Fri, 26 Jun 2015 11:57:10 GMT
Your time zone: 26 June 2015 12:57:10 GMT+1:00 DST

That’s because we need to work in UTC using SYS_EXTRACT_UTC, i.e.

with now as
(select systimestamp                  now_ts
 ,      sys_extract_utc(systimestamp) now_utc
 ,      ((extract( day    from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*24*60*60)
       + (extract( hour   from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60*60)
       + (extract( minute from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60)
       + (round(extract( second from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY')))))*1000
         now_epoch
 from dual)
select *
from   now;

NOW_TS                              NOW_UTC             NOW_EPOCH
----------------------------------- ------------------ -------------
26-JUN-15 12.03.35.231688000 +01:00 26-JUN-15 11.03.35 1435316626000 

Better!

This came about because there is a table storing epoch/unix time format which is originating from Java code and the developer said that the conversion was losing 1 hour and speculated that the DB might be “unsafe” when dealing with epoch time.

So, let’s convert this number back to a date or a timestamp and crush that notion.

with now as
(select systimestamp                  now_ts
 ,      sys_extract_utc(systimestamp) now_utc
 ,      ((extract( day    from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*24*60*60)
       + (extract( hour   from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60*60)
       + (extract( minute from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60)
       + (round(extract( second from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY')))))*1000
         now_epoch
 from dual)
select now_ts
,      now_utc
,      now_epoch
,      TO_TIMESTAMP('01-01-1970','DD-MM-YYYY') + NUMTODSINTERVAL(now_epoch/1000,'SECOND') 
       epoch_back_to_ts
from   now;

NOW_TS                              NOW_UTC             NOW_EPOCH    EPOCH_BACK_TO_TS 
----------------------------------- ------------------ ------------- ------------------
26-JUN-15 12.09.45.671605000 +01:00 26-JUN-15 11.09.45 1435316986000 26-JUN-15 11.09.46 

Our conversion back is still in UTC so we need to convert, and there are numerous ways that we might want to convert that back:

with now as
(select systimestamp                  now_ts
 ,      sys_extract_utc(systimestamp) now_utc
 ,      ((extract( day    from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*24*60*60)
       + (extract( hour   from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60*60)
       + (extract( minute from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY'))*60)
       + (round(extract( second from sys_extract_utc(systimestamp) - TO_TIMESTAMP('01/01/1970', 'MM/DD/YYYY')))))*1000
         now_epoch
 from dual)
select now_ts
,      now_utc
,      now_epoch
,      TO_TIMESTAMP('01-01-1970','DD-MM-YYYY') + NUMTODSINTERVAL(now_epoch/1000,'SECOND') 
       epoch_back_to_utc
       ,      CAST(
       FROM_TZ(TO_TIMESTAMP('01-01-1970','DD-MM-YYYY') + NUMTODSINTERVAL(now_epoch/1000,'SECOND'),'UTC')
            AT TIME ZONE 'Europe/London' AS TIMESTAMP) 
       back_to_name
,      CAST(
       FROM_TZ(TO_TIMESTAMP('01-01-1970','DD-MM-YYYY') + NUMTODSINTERVAL(now_epoch/1000,'SECOND'),'UTC')
            AT LOCAL AS TIMESTAMP) 
       back_to_local
,      CAST(
       FROM_TZ(TO_TIMESTAMP('01-01-1970','DD-MM-YYYY') + NUMTODSINTERVAL(now_epoch/1000,'SECOND'),'UTC')
            AT TIME ZONE DBTIMEZONE AS TIMESTAMP) 
       back_to_dblocal
FROM   now;

NOW_TS                              NOW_UTC             NOW_EPOCH     EPOCH_BACK_TO_UTC  BACK_TO_NAME       BACK_TO_LOCAL      BACK_TO_DBLOCAL  
----------------------------------- ------------------ -------------- ------------------ ------------------ ------------------ ------------------
26-JUN-15 12.12.23.936868000 +01:00 26-JUN-15 11.12.23 1435317144000  26-JUN-15 11.12.24 26-JUN-15 12.12.24 26-JUN-15 12.12.24 26-JUN-15 12.12.24 

SQL Developer 3 and Timestamp Conversion

I shouldn’t be using an old version of SQL Developer like v3 (3.2.10.09) anyway but… cut a long story short … I am.
I’ve also got v4 which doesn’t display the same problem

Just pointing out an oddity whilst investigating something for someone:

with x as 
(select to_date('28-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt1
 ,      to_date('29-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt2
 from   dual)
select sessiontimezone
,      dt2 "date"
,      cast(dt2 as timestamp) "timestamp"
,      to_Char(cast(dt2 as timestamp),'DD-MON-YYYY HH24:MI') "string"
from   x;

Run as Script (F5):

SESSIONTIMEZONE date               timestamp                       string                   
--------------- ------------------ ------------------------------- -----------------
Europe/London   29-MAR-2015 01:30  29-MAR6-2015 01.30.00.000000000 29-MAR-2015 01:30

Run Statement (F9):

Europe/London   29-MAR-2015 01:30   29-MAR-2015 02.30.00.000000000 29-MAR-2015 01:30

Spot the oddity in column “timestamp” in the “Run Statement (F9)” output.

There is something dodgy going on related to timestamp display.

Colleagues in other regions have not had the same problem so I wonder if some environment variable is/isn’t getting passed along for one of these tool/driver code paths.

I’ve seen dodgy timestamp behaviour before when trying to run dbms_xplan.display_cursor(null,null) in SQL Developer and getting the plan not of my target statement but of some timestamp lookup somewhere – either tool or driver.

For example, if I run the statement above together with a dbms_xplan select but this time via “Run As Script (F5)”, e.g.

with x as 
(select to_date('28-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt1
 ,      to_date('29-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt2
 from   dual)
select sessiontimezone
,      dt2 "date"
,      cast(dt2 as timestamp) "timestamp"
,      to_Char(cast(dt2 as timestamp),'DD-MON-YYYY HH24:MI') "string"
from   x;

select * from table(dbms_xplan.display_cursor(null,null));

Then apparently my last run script (the meaning of the first null parameter to display_cursor) is:

SQL_ID  0gzt83m5pxufx, child number 0 
------------------------------------- 
/*+ NO_SQL_TRANSLATION */SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT' 
 
Plan hash value: 1805486652 
 
-------------------------------------------------------------------------- 
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |                  |       |       |     1 (100)| 
|*  1 |  FIXED TABLE FULL| X$NLS_PARAMETERS |     1 |    31 |     0   (0)| 
-------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter(("PARAMETER"='NLS_TIMESTAMP_FORMAT' AND 
              "PARAMETER"<>'NLS_SPECIAL_CHARS' AND "INST_ID"=USERENV('INSTANCE')))

Unfortunately, this could all largely be irrelevant to the OP’s question which was why does the following code return the wrong result:

select CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp)
from dual;
29-MAR-2015 02.30.00.000000000

But if we run this:

select CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp)
,      to_char(CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp),'DD-MON-YYYY HH24:MI')
from dual;
29-MAR-2015 02.30.00.000000000	29-MAR-2015 01:30

Then perhaps it suggests that this is just this tool or jdbc error and not something related to DST timezone data files, etc?

Also, there’s something about timestamps with (local) time zones which always causes trouble and which you’re forced to relearn every time… maybe it’s just me?

Dates, Timestamps and ISO 8601

If there’s one issue that comes back time and again in Oracle, it’s characterset conversions and timezones. If there are two things that come back time and time again in Oracle..

Someone asked me today how to format a date in Oracle into ISO8601 format.
Specifically the format:


2007-05-15T16:45:00Z

First reaction was what is ISO8601?

Turns out that this format is

Complete date plus hours, minutes, seconds and a decimal fraction of asecond
YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)

From an Oracle perspective, I found a couple of resources from the community.

I wondered about the Z – stands for Z time. Essentially, the same timezone as GMT and UTC.

Anyway, supposedly, to get an ISO 8601 format timestamp we need a timestamp with timezone.

So, it seems that, fair enough, we need to go from date -> timestamp -> timestamp with timezone and then we should be able to get the right format.

Date -> Timestamp is easy using TO_TIMESTAMP.

And we can get from Timestamp -> Timestamp with timezone using FROM_TZ.

What about that format we needed?

Well, it’s then that we seem to fall short.
We can’t get the ‘Z’ but seeing as that is just the equivalent of ‘+00:00′ then the latter is perfectly acceptable as is any other offset. The format mask of TZH:TZM does for this.

But the ‘T’ is problematic. Remember:


2007-05-15T16:45:00Z

Unless, not for the first time, I’m mistaken, this isn’t possible without using some horrible string concatenation.
Which is slightly odd because a search of the 10gR2 documentation shows that the XML Developer’s Kit knows something about this format.

Of course, one simple(ton) “solution” from a date would be:


select to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'HH24:MI:SS')||'Z'
from dual

That ‘Z’ is only valid for those Z-time timezones, but hey, I’m alright Jack, I’m in GMT.

Or rather, of course, if you need a timezone, use a timestamp with timezone.

Follow

Get every new post delivered to your Inbox.

Join 85 other followers