2

I have a table which one of the columns is of type TIMESTAMP(6).

How do I get the milliseconds of the column's data since 1970 UTC?

PKirby
  • 859
  • 3
  • 16
  • 36
AlikElzin-kilaka
  • 34,335
  • 35
  • 194
  • 277
  • Do you want to take into account leap seconds? [Oracle doesn't](http://stackoverflow.com/questions/31136211/). – MT0 Jul 27 '15 at 12:03

4 Answers4

2

Try this

select (cast(your_column as date) - date '1970-01-01')*24*60*60 from your_table;
StephaneM
  • 4,779
  • 1
  • 16
  • 33
  • 4
    This is seconds since 1970 not milliseconds. For milliseconds you want: `SELECT (CAST(SYSTIMESTAMP AS DATE) - DATE '1970-01-01')*24*60*60*1000 + MOD( EXTRACT( SECOND FROM SYSTIMESTAMP ), 1 ) * 1000 FROM DUAL` – MT0 Oct 18 '17 at 09:11
  • Reformatting previous comment into: `SELECT ((TRUNC(CURRENT_DATE, 'MI') - DATE '1970-01-01') * 86400 + EXTRACT(SECOND FROM CURRENT_TIMESTAMP)) * 1000 FROM DUAL` – Hans Henrik Eriksen Apr 08 '21 at 12:44
1
select 
  MY_TS as orig
  , extract(day from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60*60*24
  + extract(hour from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60*60
  + extract(minute from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60
  + extract(second from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000
    as millis
from MY_TABLE;
AlikElzin-kilaka
  • 34,335
  • 35
  • 194
  • 277
  • Please notice that if you timestamp has a different timezone than the client, you'd probably want to specify what it is. For that, replace `MY_TS` with `FROM_TZ(MY_TS, 'PST')` – AlikElzin-kilaka Jul 27 '15 at 12:07
1

Taking into account leap seconds:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE FUNCTION milliseconds_Since_1970(
  datetime TIMESTAMP
) RETURN NUMBER DETERMINISTIC
AS
  diff   INTERVAL DAY(9) TO SECOND(9) := datetime - TIMESTAMP '1970-01-01 00:00:00';
  millis NUMBER(38,0) := EXTRACT( DAY    FROM diff ) * 24 * 60 * 60 * 1000
                       + EXTRACT( HOUR   FROM diff )      * 60 * 60 * 1000
                       + EXTRACT( MINUTE FROM diff )           * 60 * 1000
                       + EXTRACT( SECOND FROM diff )                * 1000;
  leap_seconds SYS.ODCIDATELIST := SYS.ODCIDATELIST(
    DATE '1972-07-01',
    DATE '1973-01-01',
    DATE '1974-01-01',
    DATE '1975-01-01',
    DATE '1976-01-01',
    DATE '1977-01-01',
    DATE '1978-01-01',
    DATE '1979-01-01',
    DATE '1980-01-01',
    DATE '1981-07-01',
    DATE '1982-07-01',
    DATE '1983-07-01',
    DATE '1985-07-01',
    DATE '1988-01-01',
    DATE '1990-01-01',
    DATE '1991-01-01',
    DATE '1992-07-01',
    DATE '1993-07-01',
    DATE '1994-07-01',
    DATE '1996-01-01',
    DATE '1997-07-01',
    DATE '1999-01-01',
    DATE '2006-01-01',
    DATE '2009-01-01',
    DATE '2012-07-01',
    DATE '2015-07-01',
    DATE '2017-01-01'
  );
BEGIN
  FOR i IN 1 .. leap_seconds.COUNT LOOP
    IF datetime < leap_seconds(i) THEN
      RETURN millis;
    END IF;
    millis := millis + 1000;
  END LOOP;
  RETURN millis;
END;
//

Query 1:

SELECT milliseconds_Since_1970( TIMESTAMP '1970-01-01 00:00:00' ) AS "MS 1970-01-01 00:00:00",
       milliseconds_Since_1970( TIMESTAMP '1970-01-01 00:01:00' ) AS "MS 1970-01-01 00:01:00",
       milliseconds_Since_1970( TIMESTAMP '1972-06-30 23:59:59' ) AS "MS 1972-06-30 23:59:59",
       milliseconds_Since_1970( TIMESTAMP '1972-07-01 00:00:00' ) AS "MS 1972-07-01 00:00:00"
FROM   DUAL

Results:

| MS 1970-01-01 00:00:00 | MS 1970-01-01 00:01:00 | MS 1972-06-30 23:59:59 | MS 1972-07-01 00:00:00 |
|------------------------|------------------------|------------------------|------------------------|
|                      0 |                  60000 |            78796799000 |            78796801000 |
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thank you for the response. I have to say that after working with Oracle for a really short while - this is one of the least user-friendly databases I've ever seen. Truly baffling that this is not included as system utility function. – Rade_303 Oct 18 '17 at 08:27
  • 1
    While this formally answers the question, I wonder if what the OP really wanted was [unix time](https://en.wikipedia.org/wiki/Unix_time) where each day is always 86400. In other words: taking leap seconds into account is rarely what people want when they ask "how to get (milli)seconds since epoch" question. What they really want is Unix Time. Just my experience. – peterh Aug 25 '22 at 11:33
  • besides most probably not what the OP really wanted, doesn't feel very future-proof. (well, actually not even past-proof by now...). Might worth a mention – vmatyi Feb 06 '23 at 11:43
1

I've posted here some methods to convert timestamp to nanoseconds and nanoseconds to timestamp. These methods are not affected by time zones and have a nanosecond precision.

You just need to adjust it to get milliseconds instead of nanoseconds.

SELECT (EXTRACT(DAY FROM (
    SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    SYSTIMESTAMP --Replace line with desired timestamp
)) *  1000 AS MILLIS FROM DUAL;

MILLIS
1598434427263.027
Danton Estevam Pinto
  • 47
  • 2