Converting from Unix Timestamp to PostgreSQL Timestamp or Date

Sunday, November 18. 2007

Converting from Unix Timestamp to PostgreSQL Timestamp or Date

A lot of databases structures people setup seem to store dates using the Unix Timestamp format (AKA Unix Epoch). The Unix Timestamp format in short is the number of seconds elapse since january 1, 1970. The PostgreSQL timestamp is a date time format (for those coming from SQL Server and MySQL) and of course is much richer than Unix Timestamp.

Question: How do you convert this goofy seconds elapsed since January 1, 1970 to a real date?

Answer:

PostgreSQL has lots of nice date time functions to perform these great feats. Lets say someone handed us a unix timestamp of the form 1195374767. We can convert it to a real date time simply by doing this: SELECT TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second'.

Now lets say we had a table of said goofy numbers and we prefer real date structures. We can create a view that shields us from this mess by doing the following

CREATE OR REPLACE VIEW vwno_longer_goofy AS
SELECT goof.id, goof.stupid_timestamp, 
      TIMESTAMP 'epoch' + goof.stupid_timestamp * INTERVAL '1 second' as ah_real_datetime
FROM goof

UPDATE - 2010-08-21 as noted by a user below. In newer versions to_timestamp is shorter and easier:


SELECT to_timestamp(1195374767);

To convert back to unix timestamp you can use date_part:


SELECT date_part('epoch',CURRENT_TIMESTAMP)::int

Posted by Leo Hsu and Regina Obe in beginner, q&a at 11:52 | Comments (16) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

This is really nice.
Is there anything simpler? For example: abstime(1195374767) .

#1 psql (Homepage) on 2009-01-16 17:29 (Reply)

The main problem with using abstime is that I think the abstime datatype is deprecated and that function doesn't return a timestamp but rather an abstime.

The other problem which may or may not be good is that the time is relative to UTC. So observe

TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second'
gives you --- 2007-11-18 08:32:47

While abstime(1195374767) gives you an abstime output that looks like (well it would be different depending on your time zone -- I happen to be in Boston so:
2007-11-18 03:32:47-05

So abstime is more like the construct

SELECT TIMESTAMP WITH Time Zone 'epoch' + 1195374767 * INTERVAL '1 second'

Which yields a timestamp with timezone data type of

2007-11-18 03:32:47-05

#1.1 Leo on 2009-01-19 01:01 (Reply)

For what it's worth unix timestamps, while not readily readable, are hardly "goofy" or not "rich". They have a number of properties that make them the timestamp of choice in many database applications just because of the sheer number of headaches they avoid. Desirable properties include:

- There's NEVER any confusion over which timezone you mean to have associated with a unix/epoch seconds time. There's no such thing and nothing extra to try to drag along or accidentally omit.

- The epoch seconds value for every location on earth at a given moment is identical. This is incredibly useful as soon as you start recording data from multiple timezones, or if you ever move data around.

- Calculating time deltas between timestamps is trivial.

- Unix time/epoch seconds value can be "rendered" to whichever locale is desired. Timezone, DST and all the headaches that go with tracking them become display time details...the backend data will always be correct and not need updates everytime someone changes their DST rules.

#2 billm on 2009-03-05 16:07 (Reply)

I hear you brother - I hear you! Praise da epoch!

#2.1 LimoDriver on 2009-03-08 06:35 (Reply)

Why all this '... * INTERVAL '1 second' as ...' conversion mess?

Use the integrated psql to_timestamp function:

select(to_timestamp(61));
1970-01-01 01:01:01+01

(I'm in GMT+1)

#3 LimoDriver on 2009-03-08 06:42 (Reply)

Great article! Today i found a really cool tool to convert unix time directly from the mysql phpMyAdmin. check out www.convert-unix-time.com and the bookmarklet.

i used it only today four times, love it! :-)

regards,
tom

#4 Tom Beckmark on 2011-05-26 11:04 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: Phone* What is one plus six?
	Remember Information? Subscribe to this entry

Converting from Unix Timestamp to PostgreSQL Timestamp or Date

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting

Sunday, November 18. 2007

Converting from Unix Timestamp to PostgreSQL Timestamp or Date

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Converting from Unix Timestamp to PostgreSQL Timestamp or Date

Postgres OnLine Journal PostGIS in Action About the Authors Consulting

Sunday, November 18. 2007

Converting from Unix Timestamp to PostgreSQL Timestamp or Date

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting