CODEWITHSUNDEEP
oracle-databasetimestampiso8601
Mendes
Mendes

Reputation: 18451

Unix Timestamp to ISO-8601 String

I have an Oracle table called ACQDATA with a field READDATETIME where I store a Unix timestamp in milliseconds as an INTEGER (NUMBER(38)) type.

SQL> select READDATETIME from ACQDATA where ID=1000;

READDATETIME
____________
   1.4793E+12

I need to select that value as a ISO-8601 string (YYYY-MM-DDTHH:MM:SS.mmm):

SQL> select READDATETIME from ACQDATA where ID=1000;

READDATETIME
-------------------
   1.4793E+12

I´ve tried to convert it using TO_CHAR, but the result is messy:

   SQL> select TO_CHAR(TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(READDATETIME, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') from ACQDATA where ID=1000;

Error at line 1:
ORA-01873: the leading precision of the interval is too small

Help appreciated.

Upvotes: 2

Views: 2439

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Your readdatetime seems to be in milliseconds. Oracle date arithmetic works on the basis of days, so you need to convert that number to the number of days it represents; one day is 86400 seconds, so it's 86400000 milliseconds:

with acqdata (id, readdatetime) as (
  select 1000, 1479318995000 from dual
)
select to_char(date '1970-01-01' + (READDATETIME/86400000), 'YYYY-MM-DD"T"HH24:MI:SS')
from ACQDATA where ID=1000;

TO_CHAR(DATE'1970-0
-------------------
2016-11-16T17:56:35

The T is added as a character literal.

SQL Developer defaults to show numbers that large in scientific notation. You can change that default with set numformat, or use to_char() to show the whole value:

select readdatetime, to_char(readdatetime, '9999999999999') as string
from ACQDATA where ID=1000;

READDATETIME STRING        
------------ --------------
  1.4793E+12  1479318995000

If your value has fractional seconds, so the last three digits are not zeros, you can convert the date to a timestamp and add on the fractional leftovers; this also adds the UTC 'Z' indicator for fun:

with acqdata (id, readdatetime) as (
  select 1000, 1479300462063 from dual
)
select to_char(cast(date '1970-01-01' + (readdatetime/86400000) as timestamp)
    + numtodsinterval(remainder(readdatetime, 1000)/1000, 'SECOND'),
  'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
from acqdata where id=1000;

TO_CHAR(CAST(DATE'1970-01-01'+
------------------------------
2016-11-16T12:47:42.063Z

Or without the intermediate date value, starting from a timestamp literal:

with acqdata (id, readdatetime) as (
  select 1000, 1479300462063 from dual
)
select to_char(timestamp '1970-01-01 00:00:00'
  + numtodsinterval(readdatetime/1000, 'SECOND'),
  'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
from acqdata where id=1000;

TO_CHAR(TIMESTAMP'1970-0
------------------------
2016-11-16T12:47:42.063Z

As @Wernfried ponted out, it's better to explicitly show that the epoch time is starting from UTC:

alter session set time_zone='America/New_York';

with acqdata (readdatetime) as (
  select 1479300462063 from dual
  union all select 1467331200000 from dual
  union all select 1467648000000 from dual
)
select readdatetime,
  to_char(timestamp '1970-01-01 00:00:00' + numtodsinterval(readdatetime/1000, 'SECOND'),
    'YYYY-MM-DD"T"HH24:MI:SS.FF3') as implicit,
  to_char(cast(timestamp '1970-01-01 00:00:00' as timestamp with time zone)
    + numtodsinterval(readdatetime/1000, 'SECOND'),
    'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') as local_offset,
  to_char(timestamp '1970-01-01 00:00:00 UTC' + numtodsinterval(readdatetime/1000, 'SECOND'),
    'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') as utc_offset,
  to_char(timestamp '1970-01-01 00:00:00 UTC' + numtodsinterval(readdatetime/1000, 'SECOND'),
    'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR') as utc
from acqdata;

  READDATETIME IMPLICIT                LOCAL_OFFSET                  UTC_OFFSET                    UTC                       
-------------- ----------------------- ----------------------------- ----------------------------- --------------------------
 1479300462063 2016-11-16T12:47:42.063 2016-11-16T12:47:42.063-05:00 2016-11-16T12:47:42.063+00:00 2016-11-16T12:47:42.063UTC
 1467331200000 2016-07-01T00:00:00.000 2016-07-01T01:00:00.000-04:00 2016-07-01T00:00:00.000+00:00 2016-07-01T00:00:00.000UTC
 1467648000000 2016-07-04T16:00:00.000 2016-07-04T17:00:00.000-04:00 2016-07-04T16:00:00.000+00:00 2016-07-04T16:00:00.000UTC

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Alex's answer is not fully correct. Unix timestamp is always based on 1970-01-01 00:00:00 UTC

Unless your session runs on UTC time zone the precise solution would be like this:

select 
   TO_CHAR((TIMESTAMP '1970-01-01 00:00:00 UTC' + readdatetime/1000 * INTERVAL '1' SECOND) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;

or

select 
   TO_CHAR((TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + readdatetime/1000 * INTERVAL '1' SECOND) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;

or if you prefer functions instead of literals:

select 
   TO_CHAR((TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR') + numtodsinterval(readdatetime/1000, 'SECOND')) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;

Upvotes: 4

Related Questions