[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)