[Babase] Babase dates/times, a collection of notes

Stephanie Combes babase@www.eco.princeton.edu
Mon, 11 Oct 2004 17:19:37 -0400


My temporary system is to print out some of these "helpful hints" emails 
and file them with the conversion notebook I have.  It will probably need 
better organization, but for now it at least keeps track of a lot of the 
information that's going back and forth.
-s

--On Saturday, October 09, 2004 8:55 AM +0300 Susan Alberts 
<alberts@duke.edu> wrote:

> Steph (and Daphne?) --- please see my note below where karl provides us
> with an example query for extracting time and date from timestamp in
> postgresql
>
>>>>
>>>> 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?)
>
>
> I think the answer is  yes you should be converting to 1 if it is easy
> but i don't have a good enough feel for what the data look like.
>
>>
>>>>
>>>> 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#FUNC
>>>> TIONS-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
>
>
> Steph and Daphne, it seems to me that we might want to start a collection
> of resource information for how to start querying postgresql in areas
> where it will be different than foxpro. It would be great if you two
> could discuss how to best implement and share such a document, it can be
> on the babase list and then we can all give input, or you can just start
> an electronic archive on nyani2 or something.
>
> Thanks,
> Susan
>
>>
>>
>> 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)
> _______________________________________________
> Babase mailing list
> Babase@www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase