[Babase] Babase dates/times, a collection of notes
Susan Alberts
babase@www.eco.princeton.edu
Sat, 9 Oct 2004 08:55:58 +0300
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#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
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)