Renier Schutte Renier Schutte - 2 months ago 8
SQL Question

How do I convert this value into a datetime value I can understand in SQL

I am trying to write report in SQL for a ticket database and i need to filter on the date and time.

Unfortunately the values in the date time fields dont make sense to me.

How can I convert

1473140017153
to a
datetime
value?

Here are the strings and the dates and time that correspond to them.

1473140017153 06/09/2016 07:33 AM
1473140228660 06/09/2016 07:37 AM

Answer

We have no clue what RDBMS you are using, forcing us to basically give you answers for all possibilities:

SQL Server:

DATEADD(ms, col, '19700101')

or

DATEADD(s, col / 1000, '19700101')

Oracle:

TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(col / 1000, 'SECOND')

MySQL:

FROM_UNIXTIME(col / 1000)

Postgres:

TO_TIMESTAMP(col / 1000)

If you're using SQLite, you're out of luck :-)

Comments