[Babase] Babase dates/times, a collection of notes
Karl O. Pinc
babase@www.eco.princeton.edu
Wed, 6 Oct 2004 16:51:41 -0500
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?
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
)
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.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein