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.
Add a comment
|
3 Answers
If your data is stored in a column called ts, in a table called data, do this:
select extract(epoch from ts) from data
3 Comments
Frank Heikens
It's all in the manual: postgresql.org/docs/current/interactive/…
OwlCodR
How fast is it?
Joe Love
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.
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;
Comments
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
