-4

I want to insert the current time in millis ( epoch ) to a table. Is there any built in function that I can use which gives me the time in epoch?

Ajay
  • 2,976
  • 2
  • 28
  • 35
  • 3
    http://stackoverflow.com/search?q=[oracle]+epoch –  May 13 '15 at 10:13
  • 1
    What precision do you need for the current time - in seconds (from sysdate, so your epoch value always ends in 000) or milliseconds (from systimestamp)? That will slightly effect which question this is a duplicate of. – Alex Poole May 13 '15 at 10:31
  • 1
    To answer the question you asked - no, there's no built-in function to provide the time in milliseconds since midnight on 01-JAN-1970. – Bob Jarvis - Слава Україні May 13 '15 at 10:55
  • The question is worded as if you want to convert the current Oracle time into an epoch value; the duplicate you picked is going the other way. Which are you trying to do? – Alex Poole May 13 '15 at 11:19
  • OP's question is converting date to epoch, the question picked as duplicate is vice-versa. – Lalit Kumar B May 14 '15 at 03:16
  • from https://currentmillis.com/ `SELECT (SYSDATE-CAST(TO_TIMESTAMP_TZ('01-01-1970 00:00:00+00:00', 'DD-MM-YYYY HH24:MI:SS TZH:TZM') as date)) * 24 * 60 * 60 * 1000 FROM DUAL;` – Sylvain Mar 04 '20 at 14:40

2 Answers2

5

This will give you the milli seconds since 01-Jan-1970:

select (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy')) *24*60*60*1000 from dual

ennovation
  • 366
  • 2
  • 9
  • 4
    It's good practice to not use language dependent date formats. Your query would fail e.g. on a french computer. Better use an ISO format e.g. `1970-01-01` –  May 13 '15 at 10:35
  • @a_horse_with_no_name - good point. Can you suggest a date format which is language agnostic and which doesn't suffer from the problem of potentially confusing month and day, when these last two quantities are expressed as number? – Bob Jarvis - Слава Україні May 13 '15 at 10:44
  • 1
    @BobJarvis ANSI date literal `DATE 'YYYY-MM-DD'` Or, explicitly mention the `NLS_DATE_LANGUAGE` parameter value. Or, am I missing something in your question? – Lalit Kumar B May 13 '15 at 10:46
  • @LalitKumarB - agreed that if you know which format was used you're fine, but consider 2015-03-07 - is that March 7th or July 3rd? That's why I prefer 'DD-MON-YYYY' (also known as "military format") - it's unambiguous. However, as was pointed out before, it's not language agnostic. For my purposes I can live with a lack of language agnosticity, but can't live with two two-digit numbers next to each other and having to trust that I guessed the format correctly. YMMV. – Bob Jarvis - Слава Україні May 13 '15 at 10:51
  • 2
    @BobJarvis: ANSI date literals (and the ISO formats) require `yyyy-mm-dd`. `DATE '2015-03-07'` is not ambiguous by definition. And I have never seen a date specified as `yyyy-dd-mm` to be honest. If the date starts with the year it's always (in my experience) an ISO format. –  May 13 '15 at 10:59
  • 1
    @BobJarvis: That's why the ANSI/ISO date literal specifies only one format `DATE 'yyyy-mm-dd'`, which happens to be the international standard for dates :-) – dnoeth May 13 '15 at 11:01
  • In cases where the date format is literally next to the date string, as is the case here, there is no ambiguity in purely numeric formats. – Steve Bosman Sep 21 '16 at 08:54
2

Keeping in mind the timezone:

SQL> SELECT
  2     EXTRACT(DAY FROM (from_tz(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'UTC' -TIMESTAMP '1970-01-01 00:00:00 +00:00'))*86400+
  3     EXTRACT(HOUR FROM (from_tz(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'UTC' -TIMESTAMP '1970-01-01 00:00:00 +00:00'))*3600+
  4     EXTRACT(MINUTE FROM (from_tz(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'UTC' -TIMESTAMP '1970-01-01 00:00:00 +00:00'))*60+
  5     EXTRACT(SECOND FROM (from_tz(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'UTC' -TIMESTAMP '1970-01-01 00:00:00 +00:00')) date_to_epoch
  6  FROM dual;

DATE_TO_EPOCH
-------------
   1431513604
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124