up vote 0 down vote favorite

PostgreSQL field type for unix timestamp :

  • to store it as unix time stamp
  • to retrieve it as a unix timestamp as well.

Have been going through Date/Time Types postgreSQL V 9.1.

  • Is integer the best way to go!? (this is what I had done when I was using MySQL. Had used int(10))
share|improve this question

3 Answers

up vote 1 down vote accepted

integer would be good, but not enough good, because postgresql doesn't support unsigned types

share|improve this answer
I think I can counter that with a check to make it behave like an unsigned number. – ThinkingMonkey Aug 3 '12 at 16:01
until 2038 this is not a problem :) – CyberDem0n Aug 3 '12 at 16:04
probably use bigint then. But it would not be good to store the extra bits. – ThinkingMonkey Aug 3 '12 at 16:25
1  
Maia storage really so expensive that you need to worry about a couple of bytes extra per row? – GordonM Aug 3 '12 at 16:38
@GordonM you are right. It is not. :) – ThinkingMonkey Aug 3 '12 at 16:38
up vote 6 down vote

I'd just go with using TIMESTAMP WITH(OUT) TIME ZONE and use EXTRACT to get a UNIX timestamp representation when you need one.

Compare 

SELECT NOW();

with 

SELECT EXTRACT(EPOCH FROM NOW());
share|improve this answer
As I am saying, I want to store Unix timestamps. Does the timestamp datatype store the literal unix timestamp? – ThinkingMonkey Aug 3 '12 at 16:02
TIMESTAMP WITH(OUT) TIME ZONE does not store the literal timestamps. – ThinkingMonkey Aug 3 '12 at 16:30
I really don't think the downvote was called for. Postgres doesn't have a data type that literally maps to unix timestamp. You have to either use postgres timestamp or a non-temporal type. If you do the latter you won't be able to use SQL to compare timestamps – GordonM Aug 3 '12 at 16:33
5  
Then convert to Postgres timestamp on store and convert back (with extract like I showed you) when you fetch the data back. It's a better solution than bodging it with an int or string – GordonM Aug 3 '12 at 16:40
3  
By default TIMESTAMP WITH TIME ZONE is stored as a 64-bit integer of the microseconds from midnight 2000-01-01 UTC (ignoring leap seconds, so really more like UT1). That converts pretty quickly and easily to Unix time, while allowing easy presentation and date math. If you really want the coarser resolution, integer presentation, and difficulty in date calculations, feel free to use bigint or numeric. There aren't too many situations where that's a net win, but maybe you have one of those few. – kgrittn Aug 3 '12 at 19:25
show 1 more comment
up vote 1 down vote

I do not understand why the question has some negative votes.

Anyway, I have found a closely related question in the Database Administrators site (with many positive votes).

This is just to suggest to have a look there, since there is a much more complete information about this non-trivial topic.

share|improve this answer
Thanks for the link. and for mentioning that this is a non-trivial topic. +1. – ThinkingMonkey Dec 28 '12 at 16:24

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.