| |||||||||||||
|
| |||||||||||||
Haas: Big ideas [for PostgreSQL]Haas: Big ideas [for PostgreSQL]Posted May 6, 2010 0:03 UTC (Thu) by leonov (subscriber, #6295)In reply to: Haas: Big ideas [for PostgreSQL] by arjan Parent article: Haas: Big ideas [for PostgreSQL]
What I do is to simply use UNIX epoch timestamps for time/date storage. They are just integers, so take little space and index well, and have other benefits too.
Sounds crazy at first blush, but has worked out great for me over the years. I wrote a short article about them here:
(Log in to post comments)
Haas: Big ideas [for PostgreSQL] Posted May 6, 2010 3:10 UTC (Thu) by jhardin@impsec.org (guest, #15045) [Link] ...that's the sort of thing the db engine should be doing for you.
Haas: Big ideas [for PostgreSQL] Posted May 10, 2010 16:11 UTC (Mon) by intgr (subscriber, #39733) [Link]
And it does, the original poster is simply confused.
Haas: Big ideas [for PostgreSQL] Posted May 6, 2010 4:33 UTC (Thu) by wahern (subscriber, #37304) [Link]
Your web page assumes that `time_t' stores a Unix Timestamp--number of seconds from the epoch 1970-01-01. But the C standard has no such requirement, either concerning the epoch or whether time_t stores second units--C specifies difftime() to get a difference between two time_t values in seconds.
Languages such as Perl and Python which expose the C routines also do not make any such guarantees, and the documentation mentions this (actually, Python specifies the unit but leaves unspecified the epoch). If you pass a time() value from one system to another, for example by converting to an integer and inserting into a database, you can't assume that it refers to the same calendar time, or is even meaningful at all, merely because both systems are using C. Basically, the only meaningful thing you can do with a time_t value, without relying on other standards, is to use it with strftime(), difftime(), and the other date-time related C interfaces.
The only way to exchange a time_t value between systems in a portable manner is by serializing the value, for example into the ISO8601 format, though historically the asctime() and RFC822 formats were preferred.
Of course, depending on POSIX is another story altogether. But it's POSIX that's being used, not C.
Haas: Big ideas [for PostgreSQL] Posted May 6, 2010 14:46 UTC (Thu) by cruff (subscriber, #7201) [Link]
You also can not make an assumption that the time_t is even an integer, it could be floating point, which is permissible according to IEEE 1003.1.
Haas: Big ideas [for PostgreSQL] Posted May 8, 2010 19:49 UTC (Sat) by HenrikH (guest, #31152) [Link]
In theory that is correct yes, but in practice you can even export a time_t value to Windows without any problems.
Honestly you have to search very far and wide to find a system using time_t that is:
1. not using integers
And as noted, even Windows follows the rules.
|
|
||||||||||||