[Babase] Babase dates/times, a collection of notes
Susan Alberts
babase@www.eco.princeton.edu
Thu, 7 Oct 2004 18:52:27 +0300
>Hi,
>
>Sorry to repeat any past questions.
>
>We've already agreed that babase dates and times are stored
>in local Kenyan time. I've got the database storing them
>without any sort of time zone (UTC time, which is GMT aka
>Zulu time).
>
>The only problem is if there's some sort of daylight savings
>time or other time-shifting going on in the field, because
>then recorded times would shift where actual time does not,
>leading to odd differences when comparing across the
>'time shift' mark. Need I be concerned?
There is no time shift of any kind in Kenya over the course of the
year, I think this is not a concern.
>
>I also note that Psion date/times seem to be recorded to a
>millisecond accuracy. This could lead to oddities
>when looking for things, say, 1 hour apart. The psion times
>I noticed that have milliseconds are all just 1 millisecond
>off the second mark. They are all time + .999 seconds.
>
>We did some goofyness in Foxpro where we duplicated the data
>in two columns when we needed both a date and a time because
>it was hard to work with times and dates. This happened
>in INTERACT and JPSAMPS and FPSAMPS. In Postgresql I'm going
>to have one column, a timestamp column, that contains both
>the date and the time. You can do things like cut down the
>accuracy of a timestamp column so it's accurate to a second,
>minute, hour, day, week, month, year, century, etc. with
>a function called date_trunc(). (See:
>http://www.postgresql.org/docs/7.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
>)
OK -- is it easy to query on either date or time alone this way? This
is almost always what we will be doing. Ie looking for things that
happened on a particular date regardless of time, or looking for
things that happened at particular times regardless of date.
>
>So, for instance, you may always want to truncate the
>Psion data's times to prior minute mark when working
>with them. (Which neatly takes care of milliseconds
>questions, I think.)
>
>If it looks like there's difficulty working with dates and
>times there's a variety of solutions and I'd rather wait to
>see what the problem is before picking one, instead of just
>carrying forward the Foxpro solution.
OK
>Karl <kop@meme.com>
>Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>_______________________________________________
>Babase mailing list
>Babase@www.eco.princeton.edu
>http://www.eco.princeton.edu/mailman/listinfo/babase
--
--------------------------------------------------------------------------------------------------------------------------------------
Susan Alberts, Department of Biology, Duke University, Box 90338,
Durham NC 27708
919-660-7272 (phone), 919-660-7293 (FAX)