converting timestamp to date yields wrong hour

Question

I have this timestamp value: 1568387621 I need to convert it to date.

I tried something like:

SELECT TO_TIMESTAMP('19700101','yyyymmdd') + ((1568387621/1000)/24/60/60)
FROM dual;

But it doesn't work.

My timezione is Europe/Rome.

Some conversion websites tell me the value converted is: 2019-09-13 17:13:41 Instead, in the best case on db I obtain: 2019-09-13 15:13:41

Which hour is correct (15 or 17). How can I convert it correctly?

score 3 · Accepted Answer · answered Sep 16 '19 at 09:56

3

In order to cover time zone use this one:

(TIMESTAMP '1970-01-01 00:00:00 UTC' + 1568387621 * INTERVAL '1' SECOND) AT 'Europe/Rome'

answered Sep 16 '19 at 09:56

Thank you very much: SELECT (TIMESTAMP '1970-01-01 00:00:00 UTC' + 1568387621 * INTERVAL '1' SECOND) AT time zone 'Europe/Rome' FROM dual; worked fine – Ork Sep 16 '19 at 10:12

score -2 · Answer 2 · answered Sep 16 '19 at 09:40

-2

SQL Server's TIMESTAMP datatype has nothing to do with a date and time!

Read first answer here:

answered Sep 16 '19 at 09:40

Nenad Vujić

This question is not about SQL Server; it's tagged as Oracle. – Boneist Sep 16 '19 at 09:55
I confirm. It's Oracle not Sql Server – Ork Sep 16 '19 at 09:59

2 Answers2