59

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.

lospejos
  • 1,976
  • 3
  • 19
  • 35
Ashish Kumar Saxena
  • 4,400
  • 8
  • 27
  • 48

3 Answers3

118

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

select extract(epoch from ts) from data
Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • 2
    It's all in the manual: http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT – Frank Heikens Jan 02 '15 at 16:56
  • How fast is it? – OwlCodR May 03 '22 at 15:56
  • 1
    Its not slow at all, unnoticable slow down if any. Either way it has to do something to convert the time to something usable, and likely it's stored in something more similar to epoch/unix format as opposed to hh:mi:ss or whatever. – Joe Love May 04 '22 at 16:35
15

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;

haoming
  • 787
  • 7
  • 4
0

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

Navneet Khandelwal
  • 11
  • 4