Re: [SQL] Query based on date/time field

On Tue, 4 Aug 1998, William McCracken wrote:

> I am new to SQL and was wondering how I would go
> about selecting records from my database based on
> the age of a date/time stamp.
>
> For example if I wanted to select records older than
> 12 hours or 1 day.
>
> Any help would be appreciated.

Sorry for my late response, but I was on holiday.
I use some statement like this to select entries that are not older than 2
weeks:

SELECT DISTINCT landkreis FROM kneipe WHERE 1209600 >= (date_part('epoch',
current_date) - date_part('epoch',datum));

datum is of type datetime.

what I do is the following: epoch returns the seconds since january 1st
1970 (I think). I substract the timestamp of each record from the current
timestamp (current_date). The result has to be smaller than the duration
for 2 weeks (in seconds, which is 1209600s).

I dont know if there is a better solution, but it works fine.

So long,
        Holger

--------------------------------------------------------------------------------
Holger Mitterwald COI-D        ####   #####    ###       CONSULTING FUER OFFICE
COI GmbH Herzogenaurach       #      #     #    #        UND INFORMATION
Industriestrasse 1 - 3        #      #     #    #        MANAGEMENT GmbH
D-91074 Herzogenaurach         ####   #####    ###       Tel.: 09132/82-3811