Re: [despammed] timestamps

am  14.12.2004, um 12:39:44 +0300 mailte Martin Atukunda folgendes:
>
> I'm trying to implement a daemon that gets its data from postgresql.
>
> The user uses a web interface to store the time that the daemon should begin
> operations in a table called translog.
>
> The schema for translog is as follows:
>
> CREATE TABLE translog (
>  id serial,
>  action integer,
>  sdate varchar(16) NOT NULL,
>  stime varchar(16) NOT NULL
> );
>
>  id | action |   sdate    |  stime
> ----+--------+------------+----------
>   3 |      1 | 2004/12/10 | 12:30:00
>   4 |      1 | 2004/12/10 | 12:20:00
>   5 |      1 | 2004/12/13 | 12:30:00
>   6 |      1 | 2004/12/13 | 12:30:30
> (4 rows)

Why varchar(16) for date/time variables? This isn't a good idea.


>
> The daemon hits the database every minute and I would like it to get all the
> rows whose stime is between now  and one minute ago.
>
> How do I select all the rows that have an stime between now and one minute
> ago?

If you use the correct typ, you can use a interval for this job.


A example:


test=# \d stamp
              Tabelle »public.stamp«
 Spalte |             Typ             | Attribute
--------+-----------------------------+-----------
 id     | integer                     |
 ts     | timestamp without time zone |


test=# select now();
              now
-------------------------------
 2004-12-14 11:14:35.436616+01
(1 Zeile)

test=# select * from stamp ;
 id |         ts
----+---------------------
  1 | 2004-12-14 11:04:00
  2 | 2004-12-14 11:05:00
  3 | 2004-12-14 11:06:00
  4 | 2004-12-14 11:07:00
(4 Zeilen)

test=# select * from stamp where ts between now()-'00:09:00'::interval
and now();
 id |         ts
----+---------------------
  3 | 2004-12-14 11:06:00
  4 | 2004-12-14 11:07:00
(2 Zeilen)


--
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===