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

score 11 · Accepted Answer · 2016-03-31 06:56:00Z

11

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!

edited Mar 31, 2016 at 6:56

answered Mar 31, 2016 at 6:01

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Anitha Mani Over a year ago

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

user330315 Over a year ago

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

user330315 Over a year ago

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

Dean Sha Over a year ago

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.

Collectives™ on Stack Overflow

Convert timestamp column values to epoch in PostgreSQL select query

1 Answer 1

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

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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