Обсуждение: Using Epoch to save timestamps in 4 bytes?
While looking at a database I inheritted I noticed the database has tables with integers used to store epoch. I noticed that timestamp is 8 bytes and was wondering how come timestamp is 8 bytes and not 4. Is it to be able to support precission beyond a second? I am looking at tens of millions of rows, which is why my predecessor may have used integer to store epoch to save space.
Francisco Reyes wrote:
> While looking at a database I inheritted I noticed the database has tables
> with integers used to store epoch.
>
> I noticed that timestamp is 8 bytes and was wondering how come timestamp is
> 8 bytes and not 4. Is it to be able to support precission beyond a second?
>
> I am looking at tens of millions of rows, which is why my predecessor may
> have used integer to store epoch to save space.
Our timestamp has a much larger range than a 4-byte time_t, docs say:
<entry>4713 BC</entry>
<entry>294276 AD</entry>
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Thu, May 8, 2008 at 10:00 PM, Bruce Momjian <bruce@momjian.us> wrote: > > Our timestamp has a much larger range than a 4-byte time_t, docs say: > > <entry>4713 BC</entry> > <entry>294276 AD</entry> Which is normally great. Doesn't it have greater precision in the modern era or something like that? If you compile for integer dates do they have the same range? Also, that range is fine, unless you're tracking geological timeframes. :)
On Fri, May 9, 2008 at 3:15 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Thu, May 8, 2008 at 10:00 PM, Bruce Momjian <bruce@momjian.us> wrote: >> >> Our timestamp has a much larger range than a 4-byte time_t, docs say: >> >> <entry>4713 BC</entry> >> <entry>294276 AD</entry> > > Which is normally great. Doesn't it have greater precision in the > modern era or something like that? > > If you compile for integer dates do they have the same range? no. that's actually the integer range. the float range is 4713 BC to 5874897 AD. Of course, at the outer ranges of the scale, the precision is going to be really lousy. Anyways, to the OP, a 4 byte time_t is to small a type to be the timestamp. There are just too many things that need greater range/precision to make it the default. Also, postgresql does not store epoch, but it's own custom type with its own bias, etc. There is nothing wrong with storing int4 epoch in your tables to save a little space if that suits your application. merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
> There is nothing wrong with storing int4 epoch in your tables to save a
> little space if that suits your application.
... Just make sure you're safely away from the scene of the crime
before 2038 ...
regards, tom lane
Bruce Momjian writes: >> I am looking at tens of millions of rows, which is why my predecessor may >> have used integer to store epoch to save space. > > Our timestamp has a much larger range than a 4-byte time_t, docs say: > > <entry>4713 BC</entry> > <entry>294276 AD</entry> Given that all of our dates will fall within what we can store in 4bytes, what would be the easiest way to use epoch as a timestamp? Create a couple of functions so our developers can use a date representation like '20080508 12:40:00' and have the functions tranlate strings to epoch and back?
Francisco Reyes <lists@stringsutils.com> writes:
> Given that all of our dates will fall within what we can store in 4bytes,
> what would be the easiest way to use epoch as a timestamp?
Well, if you're bound and determined to create a Y2038 problem for
yourself, you could use the legacy "abstime" datatype.
Actually, that choice will blow up on you rather sooner than 2038,
since I'm sure we'll pull abstime from the system well before it
actually starts failing to represent now(). But future-proofing
doesn't seem to be part of your design goals, so you might as well
use it while it's there.
regards, tom lane