[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