Convert Unixtime to Datetime SQL (Oracle)

oracle convert unix epoch time to date

To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;

11/30/2011 5:00:00 AM

To convert that date back to milliseconds:

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 * 1000
from dual;

1322629200000

If its seconds instead of milliseconds, just omit the 1000 part of the equation:

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1322629200
from dual;

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60
from dual;

Hope that helps.

Convert Unixtime to Datetime SQL (Oracle)

There are no built-in functions. But it's relatively easy to write one. Since a Unix timestamp is the number of seconds since January 1, 1970

CREATE OR REPLACE FUNCTION unix_ts_to_date( p_unix_ts IN NUMBER )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := date '1970-01-01' + p_unix_ts/60/60/24;
  RETURN l_date;
END;

which you can see being called

SQL> select unix_ts_to_date( 1336822620 ) from dual;

UNIX_TS_TO_DATE(133
-------------------
2012-05-12 11:37:00

Oracle - Convert Unixtime to local datetime including DST (Daylight Savings Time) and vice versa

Add a utcstamp seconds to the epoch 1970-01-01 UTC (as a TIMESTAMP data type) and then use AT TIME ZONE to convert it to your desired time zone:

Oracle Setup:

CREATE TABLE your_table ( utcstamp ) AS
SELECT 1576666800 FROM DUAL

Query:

SELECT ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
         AT TIME ZONE 'Europe/Amsterdam' AS Amsterdam_Time
FROM   your_table

Output:

| AMSTERDAM_TIME                                 |
| :--------------------------------------------- |
| 2019-12-18 12:00:00.000000000 EUROPE/AMSTERDAM |

Query 2:

If you want it as a DATE then just wrap everything in a CAST:

SELECT CAST(
         ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
           AT TIME ZONE 'Europe/Amsterdam'
         AS DATE
       ) AS Amsterdam_Time
FROM   your_table

Output:

| AMSTERDAM_TIME      |
| :------------------ |
| 2019-12-18 12:00:00 |

db<>fiddle here

---

CREATE FUNCTION DATE_TO_UTCEPOCHTIME (
  dt IN DATE,
  tz IN VARCHAR2 DEFAULT 'UTC'
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN ROUND(
           ( CAST( FROM_TZ( dt, tz ) AT TIME ZONE 'UTC' AS DATE )
             - DATE '1970-01-01' )
           * 24 * 60 * 60
         );
END;
/

CREATE FUNCTION UTCEPOCHTIME_TO_DATE(
  utctime IN NUMBER,
  tz      IN VARCHAR2 DEFAULT 'UTC'
) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utctime * INTERVAL '1' SECOND )
           AT TIME ZONE tz;
END;
/

then you can do:

SELECT utcepochtime_to_date( utcstamp, 'Europe/Amsterdam' )
FROM   your_table;

Which outputs:

| UTCEPOCHTIME_TO_DATE(UTCSTAMP,'EUROPE/AMSTERDAM') |
| :------------------------------------------------ |
| 2019-12-18 12:00:00                               |

and

SELECT date_to_utcepochtime(
         DATE '2019-12-18' + INTERVAL '12:00:00' HOUR TO SECOND,
         'Europe/Amsterdam'
       ) AS utcepochtime
FROM   DUAL;

which outputs:

| UTCEPOCHTIME |
| -----------: |
|   1576666800 |

db<>fiddle here

Convert timestamp to date in Oracle SQL

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Convert timestamp datatype into unix timestamp Oracle

This question is pretty much the inverse of Convert Unixtime to Datetime SQL (Oracle)

As Justin Cave says:

There are no built-in functions. But it's relatively easy to write
one. Since a Unix timestamp is the number of seconds since January 1,
1970

As subtracting one date from another date results in the number of days between them you can do something like:

create or replace function date_to_unix_ts( PDate in date ) return number is

   l_unix_ts number;

begin

   l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
   return l_unix_ts;

end;

As its in seconds since 1970 the number of fractional seconds is immaterial. You can still call it with a timestamp data-type though...

SQL> select date_to_unix_ts(systimestamp) from dual;

DATE_TO_UNIX_TS(SYSTIMESTAMP)
-----------------------------
                   1345801660

---

In response to your comment, I'm sorry but I don't see that behaviour:

SQL> with the_dates as (
  2    select to_date('08-mar-12 01:00:00 am', 'dd-mon-yy hh:mi:ss am') as dt
  3      from dual
  4     union all
  5    select to_date('08-mar-12', 'dd-mon-yy')
  6      from dual )
  7  select date_to_unix_ts(dt)
  8    from the_dates
  9         ;

DATE_TO_UNIX_TS(DT)
-------------------
         1331168400
         1331164800

SQL>

There's 3,600 seconds difference, i.e. 1 hour.

---

Related Topics