Thread: best type for unix file system timestamp?
So, I'm recording the timestamp from some unix files. Any opinions as to whether it's best to use a "bigint" (which matches the file system data) or a "timestamp" (which might be easier to manipulate in the database)? TIA! Mark -- Mark Harrison Pixar Animation Studios
However it may be represented, it IS a timestamp, so why not use a timestamp data type if you have one available to you? That's what data types are for after all... > So, I'm recording the timestamp from some unix files. > > Any opinions as to whether it's best to use a "bigint" > (which matches the file system data) or a "timestamp" > (which might be easier to manipulate in the database)?
Mark Harrison <mh@pixar.com> wrote: > > So, I'm recording the timestamp from some unix files. > > Any opinions as to whether it's best to use a "bigint" > (which matches the file system data) or a "timestamp" > (which might be easier to manipulate in the database)? I tend to choose datatypes that lend themselves to easily and clearly viewing and manipulating the data with the command-line utility, unless there's a very good reason to do otherwise. PostgreSQL has a rich complement of operators and functions for working with timestamp values, so I'd tend to stick with that, I think. Jim
Exactly, with using 'timestamp' you gain data integrity as the database will make sure that it only accepts valid timestamps. So, if your code that you use to INSERT gets skewed someway, it will fail if you try to insert an invalid number. -Robby On Fri, 2004-07-30 at 14:26, Glen Parker wrote: > However it may be represented, it IS a timestamp, so why not use a timestamp > data type if you have one available to you? That's what data types are for > after all... > > > So, I'm recording the timestamp from some unix files. > > > > Any opinions as to whether it's best to use a "bigint" > > (which matches the file system data) or a "timestamp" > > (which might be easier to manipulate in the database)? > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Robby Russell | Owner.Developer.Geek PLANET ARGON | www.planetargon.com Portland, OR | robby@planetargon.com 503.351.4730 | blog.planetargon.com
Also, there is the ability to write code that behaves a certain way based on the data type. For example, say you have a PHP script and you want all dates that display on the page to be displayed with January 1, 2004, you could program your code in a fashion that would do this should a datatype 'timestamp' be determined as the data type. This comes in handy when you build generic things like reporting engines that you don't necessarily know what data is coming to you, but know the data types. -Robby On Fri, 2004-07-30 at 16:52, Robby Russell wrote: > Exactly, with using 'timestamp' you gain data integrity as the database > will make sure that it only accepts valid timestamps. So, if your code > that you use to INSERT gets skewed someway, it will fail if you try to > insert an invalid number. > > -Robby > > > On Fri, 2004-07-30 at 14:26, Glen Parker wrote: > > However it may be represented, it IS a timestamp, so why not use a timestamp > > data type if you have one available to you? That's what data types are for > > after all... > > > > > So, I'm recording the timestamp from some unix files. > > > > > > Any opinions as to whether it's best to use a "bigint" > > > (which matches the file system data) or a "timestamp" > > > (which might be easier to manipulate in the database)? > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match -- Robby Russell | Owner.Developer.Geek PLANET ARGON | www.planetargon.com Portland, OR | robby@planetargon.com 503.351.4730 | blog.planetargon.com