[Babase] Babase dates/times, a collection of notes
Karl O. Pinc
babase@www.eco.princeton.edu
Thu, 7 Oct 2004 11:35:07 -0500
On 2004.10.07 10:52 Susan Alberts wrote:
> There is no time shift of any kind in Kenya over the course of the
> year, I think this is not a concern.
Thanks. I bet Jeanne already told me this.
>
>>
>> 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.
Is this really right? Should I be converting the .999 to 1.0?
(Is it worth thinking about?)
>>
>> 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
>> )
I take it back about INTERACT, it has just a date and and 2 times,
no timestamps. The point samples have timestamps though.
>
>
> 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.
Yes. You can convert a timestamp, which has both date and time,
into a date or a time. The SQL standard syntax is:
CAST (jpsamps.timedate AS DATE)
but the easier Postgresql syntax is:
jpsamps.timedate::DATE
For instance:
SELECT * FROM jpsamps WHERE '13:00' >= jpsamps.datetime::TIME
AND jpsamps.datetime::TIME > '11:00';
Somehow the system's got to know you want time comparison. I _think_
that this will work too, which says the string should be a
time and lets the system figure out the other side of the comparison.
SELECT * FROM jpsamps WHERE '13:00'::TIME >= jpsamps.datetime
AND jpsamps.datetime > '11:00'::TIME;
(case does not matter, in either case. Or say TIME instead of DATE.)
If it becomes a pain, there are things called views which
can make 'virtual columns', which would then be just
the date or just the time. Really, views are 'virtual
tables'. See:
http://www.postgresql.org/docs/7.3/static/tutorial-views.html
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein