CODEWITHSUNDEEP
postgresqldatetimestamptype-conversionpostgresql-9.1
oddpodm
oddpodm

Reputation: 157

Converting string timestamp into date

I have dates in a postgres database. The problem is they are stored in a string field and have values similar to: "1187222400000" (which would correspond to 07.08.2007). I would like to convert them into readable dates usind some SQL to_date() expression or something similar but can't come up with the correct syntax to make it work.

Upvotes: 2

Views: 524

Answers (1)

Vic Colborn
Vic Colborn

Reputation: 2065

There really isn't enough information here for a conclusion, so I propose this 'scientific-wild-ass-guess' to resolve your puzzle. :)

It appears this number is UNIX 'epoch time' in milliseconds. I'll show this example as if your string field had the arbitrary name, 'epoch_milli'. In postgresql you can convert it to a time stamp using this statement:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch_milli * INTERVAL '1 millisecond';

or using this built-in postgresql function:

SELECT to_timestamp(epoch_milli / 1000)

either of which, for the example '1187222400000', produces the result 

"2007-08-15 17:00:00-07"

You can do some of your own sleuthing with quite a few values selected similarly to this:

SELECT to_timestamp(epoch_milli/1000)::DATE
FROM (VALUES (1187222400000),(1194122400000)) AS val(epoch_milli);

"Well, bollocks, man. I just want the date." Point taken.

Simply cast the timestamp to a date to discard the excess bits:

SELECT to_timestamp(epoch_milli / 1000)::DATE

Of course its possible that this value is a conversion or is relative to some other value, hence the request for a second example data point.

Upvotes: 1

Related Questions