Convert timestamp column values to epoch in PostgreSQL select query

Question

I need to convert timestamp values to epoch in a SELECT query. Please find the below sample table and expected result.

Sample table:

select * from log;

id        | 3
time      | 2016-03-30 18:44:19.189513
data1     | NONE
data3     | NONE
data4     | NONE

Expected result:

id        | 3
time      | 1459343659
data1     | NONE
data3     | NONE
data4     | NONE

Log table is having n number of rows. Please find the below version details:

select version();

version                                            
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

Solution

Use the extract() function:

select id, extract(epoch from time) as time, 
       data1, data2,data3
from log;

Apparently your column is not a timestamp column but a varchar, so you first need to cast that to a real timestamp before you can use extract()

select id, 
       extract(epoch from time::timestamp) as time, 
       data1, data2,data3
from log;

This will only work if all values in that column have the correct ISO format for a timestamp.

---

This teaches you, that you should

never store date, timestamp or time values in a varchar column!

Comments

[Anitha Mani]

I already tried this, I am getting error as "ERROR: function pg_catalog.date_part(unknown, character varying) does not exist"

[a_horse_with_no_name]

@AnithaMani: you should have included that in your question. Please edit your question and add the output of select version()

[a_horse_with_no_name]

@AnithaMani: ah I didn't read the error message carefully enough. See my updated answer

[Dean Sha]

The only other thing I would add is the time zone. In that case you also need to use the "WITH TIME ZONE' in your sql statement. For example, SELECT EXTRACT(EPOCH FROM <table column for timestamp>::TIMESTAMP WITH TIME ZONE ) AS <NEW COLUMN> FROM <table name>. Change the values in < > based on your table/column names.