Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

Question

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.

• Gareth Lyons: to_timestamp does the opposite (Epoch to datetime/timestamp).

Answer 1

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.

• Patrick Mevzek: You can find all datetime related operators and functions of PostgreSQL at postgresql.org/docs/current/static/functions-datetime.html