CODEWITHSUNDEEP
sqlsql-server
Renier Schutte
Renier Schutte

Reputation: 15

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

Upvotes: 0

Views: 881

Answers (2)

Renier Schutte
Renier Schutte

Reputation: 15

I have managed to get the correct date and time using the following.

DATEADD(hh,2,DATEADD(s, wo.COMPLETEDTIME / 1000, '19700101')) as 'date'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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 :-)

Upvotes: 2

Related Questions