3

I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone.

F.e. the date 2018-08-03 should equal 1533254400 (as of calculated here).

I've tried to_timestamp(my_date_column, 'YYYY-MM-DD') but it returns error function to_timestamp(date, unknown) does not exist. I've tried adding different variations of ::timestamp... at the end of the function call, but to no avail.

Currently I do the timestamp conversion in the application layer, but I'd like to dedicate that job to the database.

  • to_timestamp does the opposite (Epoch to datetime/timestamp). – Gareth Lyons Sep 5 '18 at 14:35
5

Alright... after a change in my approach, I decided to try to "extract" data instead of convert. Turned out there's the oh-so convenient function extract in Postgres, which does exactly what I want.

extract(EPOCH from my_date_column) as date_timestamp

Here is a more detailed explanation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.