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

PostgresqlDatetimeUnix Timestamp

Postgresql Problem Overview

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.

Postgresql Solutions

Solution 1 - Postgresql

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

select extract(epoch from ts) from data

Solution 2 - Postgresql

To add Joe's answer, you can use date_part, i think it's syntax is clearer than 'extract'.

select date_part('epoch', ts) from data;

Solution 3 - Postgresql

Adding to haoming answer, for UNIX epoch this was my approach. I also added a 180 day interval which can be changed/removed upon requirements.

> date_part('epoch', (column_name + INTERVAL '180 day')) * 1000

Categories
Recommended Postgresql Solutions
Recommended Datetime Solutions
Recommended Unix Timestamp Solutions

Previous Solution:

Convert coordinates to City name?

IosSwiftMapkitClgeocoderClplacemark

Next Solution:

How to use raw sql with ecto Repo

ElixirEcto

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAshish Kumar SaxenaView Question on Stackoverflow
Solution 1 - PostgresqlJoe LoveView Answer on Stackoverflow
Solution 2 - PostgresqlhaomingView Answer on Stackoverflow
Solution 3 - PostgresqlNavneet KhandelwalView Answer on Stackoverflow