How to convert unix epoch format date to UTC in SQL query?

Question

I am having this format of datetime 1610382439.I am looking sql query to convert it to UTC time.

The query will be different on different RDBMS, which one are you using? MySQL? PostgreSQL? Oracle? ...? — Joachim Isaksson, Nov 08 '21 at 07:03

score 1 · Accepted Answer · answered Nov 08 '21 at 09:14

1

One solution is to add those seconds to 1970-01-01. The datetime datatype does not adjust for DST:

select cast('1970-01-01' as datetime) + interval 1610382439 second
-- 2021-01-11 16:27:19

answered Nov 08 '21 at 09:14

Salman A

I am getting error Parse exception at 'datetime' while running as sql query in azure databricks notebook – Saswat Ray Nov 08 '21 at 09:28
Got it, thank you :) – Saswat Ray Nov 08 '21 at 09:30
Is it MySQL or something else? – Salman A Nov 08 '21 at 09:37
yes, this query works for me select cast('1970-01-01' as date) + interval 1610382439 second – Saswat Ray Nov 08 '21 at 09:39
You could accept the answer if it helped – Salman A Nov 08 '21 at 10:24

score 0 · Answer 2 · answered Nov 08 '21 at 07:21

0

In SQL server and .net world it called ticks. You can take a look at this link which shows how to convert it: Convert .NET Ticks to SQL Server DateTime

answered Nov 08 '21 at 07:21

Reza Akraminejad

2 Answers2