pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query based on date/time field
4+ messages / 4 participants
[nested] [flat]

* Query based on date/time field
@ 1998-08-04 14:09 William McCracken <will@sitestar.com>
  1998-08-12 12:25 ` Re: [SQL] Query based on date/time field Herouth Maoz <herouth@oumail.openu.ac.il>
  1998-08-12 12:50 ` Re: [SQL] Query based on date/time field Holger Mitterwald <mittehlg@coi.de>
  0 siblings, 2 replies; 4+ messages in thread

From: William McCracken @ 1998-08-04 14:09 UTC (permalink / raw)
  To: pgsql-sql

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.

Thanks,

Will McCracken



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Query based on date/time field
  1998-08-04 14:09 Query based on date/time field William McCracken <will@sitestar.com>
@ 1998-08-12 12:25 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1998-08-12 12:47   ` Re: [SQL] Query based on date/time field Aleksey Dashevsky <postgres@luckynet.co.il>
  1 sibling, 1 reply; 4+ messages in thread

From: Herouth Maoz @ 1998-08-12 12:25 UTC (permalink / raw)
  To: pgsql-sql

At 15:50 +0300 on 12/8/98, Holger Mitterwald wrote:


> 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.

I think the following is simpler and more intuitive:

SELECT * FROM the_table
WHERE the_date >= ('now'::datetime - '12 hours'::timespan);

This assumes that the field "the_date" in "the_table" is of type datetime.
If it isn't, just convert it by using datetime( the_date ).

The amount of time described by the type timespan is very intuitive - it
accepts, as you see, things like '12 hours', '1 day', or '2 months'. It
takes months correctly - with longer and shorter months taken into
accounts. More about it in the pgbuiltin manpage.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Query based on date/time field
  1998-08-04 14:09 Query based on date/time field William McCracken <will@sitestar.com>
  1998-08-12 12:25 ` Re: [SQL] Query based on date/time field Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1998-08-12 12:47   ` Aleksey Dashevsky <postgres@luckynet.co.il>
  0 siblings, 0 replies; 4+ messages in thread

From: Aleksey Dashevsky @ 1998-08-12 12:47 UTC (permalink / raw)
  To: pgsql-sql

I just wanted to answer the initial question, but Heruoth managed to do so 
before me:-) Anyway, I decided to play with datetime and timespan types
and was stuck with strange problem. Here it is:
If I subtract one datetime from another  everithing is going as expected:
userbase=> select '12 Jun 98'::datetime-'11 Jun 98'::datetime;
?column?
--------
@ 1 day
(1 row)

But, subtracting one date from another one I got something like int:
userbase=> select '12 Jun 98'::date-'11 Jun 98'::date;
?column?
--------
       1
(1 row)

The next query explains more clearly that type of result is "int4":
userbase=> select ('12 Jun 98'::date-'11 Jun 98'::date)::datetime;
ERROR:  function datetime(int4) does not exist

Is that what is expected to be or a bug??
( I'm running PostgreSQL 6.3.2p1-4 on Linux (SlackWare) 2.0.34 )

On Wed, 12 Aug 1998, Herouth Maoz wrote:

> At 15:50 +0300 on 12/8/98, Holger Mitterwald wrote:
> 
> 
> > 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.
> 
> I think the following is simpler and more intuitive:
> 
> SELECT * FROM the_table
> WHERE the_date >= ('now'::datetime - '12 hours'::timespan);
> 
> This assumes that the field "the_date" in "the_table" is of type datetime.
> If it isn't, just convert it by using datetime( the_date ).
> 
> The amount of time described by the type timespan is very intuitive - it
> accepts, as you see, things like '12 hours', '1 day', or '2 months'. It
> takes months correctly - with longer and shorter months taken into
> accounts. More about it in the pgbuiltin manpage.
> 
> Herouth
> 
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Query based on date/time field
  1998-08-04 14:09 Query based on date/time field William McCracken <will@sitestar.com>
@ 1998-08-12 12:50 ` Holger Mitterwald <mittehlg@coi.de>
  1 sibling, 0 replies; 4+ messages in thread

From: Holger Mitterwald @ 1998-08-12 12:50 UTC (permalink / raw)
  To: William McCracken <will@sitestar.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-08-12 12:50 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-04 14:09 Query based on date/time field William McCracken <will@sitestar.com>
1998-08-12 12:25 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-08-12 12:47   ` Aleksey Dashevsky <postgres@luckynet.co.il>
1998-08-12 12:50 ` Holger Mitterwald <mittehlg@coi.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox