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