0

I have a data source which is sending TIMESTAMP data in LONG integer format and we have to convert it into TIMESTAMP in Oracle. I can do it in JAVA very easily. However, I am not sure how can this be achieved in ORACLE. TO_TIMESTAMP is giving invalid month error.

Sample data - 1636070400000

Java snippet

import java.sql.Timestamp;

public class MyClass {
    public static void main(String args[]) {
      long timeStamp = 1636070400000L;
        Timestamp stamp = new Timestamp(timeStamp);
      System.out.println("Timestamp is "+stamp);
    }
}

Above snippet returns - 2021-11-05 00:00:00.0

I want output in similar format in SQL to manipulate the data further (YYYY-MM-DD HH24:MI:SS).

Sid
  • 582
  • 3
  • 7
  • 28

1 Answers1

1

This value seems to be Java/Javascript timestamp, i.e. number of Millisecond from 1970-01-01. Try this one:

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC' + 1636070400000/1000 * INTERVAL '1' SECOND
FROM dual;

It returns UTC time. If you like to get your local time, then use

SELECT (TIMESTAMP '1970-01-01 00:00:00 UTC' + 1636070400000/1000 * INTERVAL '1' SECOND) AT LOCAL
FROM dual;

or

SELECT (TIMESTAMP '1970-01-01 00:00:00 UTC' + 1636070400000/1000 * INTERVAL '1' SECOND) AT 'Europe/Zurich'
FROM dual;

Instead of INTERVAL literal, you can also use NUMTODSINTERVAL(1636070400000/1000, 'second')

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • its my current code from work we use : to_date('01/01/1970','dd/mm/yyyy') + (1636070400000 / (1000 * 60 * 60 * 24)) – Ali Fidanli Oct 20 '21 at 06:18
  • @AliFidanli That's also fine. However, the result is given as UTC time. If you like to get local time, then result is wrong. By using `TO_DATE` you also loose fractional seconds, which might be required. – Wernfried Domscheit Oct 20 '21 at 06:33
  • totally aggreed. just wanted to share it when saw the question :) – Ali Fidanli Oct 20 '21 at 07:09