How to convert date time into unix epoch value in Postgres?

postgresql datetime unix-timestamp
Ashish Kumar Saxena picture Ashish Kumar Saxena · Jan 2, 2015 · Viewed 34.1k times · Source

How do I convert the following format to UNIX timestamps?

A value like: 01-02-2015 10:20 PM should be converted to: 1418273999000

I did try to_timestamp function but its not working for me.

Answer

Joe Love picture Joe Love · Jan 2, 2015

If your data is stored in a column called ts, in a table called data, do this:

select extract(epoch from ts) from data

Related questions

Can't subtract offset-naive and offset-aware datetimes

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.…

Read More
How do I convert an interval into a number of hours with postgres?

Say I have an interval like 4 days 10:00:00 in postgres. How do I convert that to a number of hours (106 in this case?) Is there a function or should I bite the bullet and do something like extract(days, my_interval) * 24 + …

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More