Re: timestamps

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)

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?

- Martin -

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

# The schema for translog is as follows:
#
# CREATE TABLE translog (
# id serial,
# action integer,
# sdate varchar(16) NOT NULL,
# stime varchar(16) NOT NULL
# );

I assume you mean the composite sdate/stime is 1 minute ago?

Instead of storing separate date & time varchar fields, why don't you
store one timestamp field? Then a query like this would work:

SELECT * FROM translog WHERE start <= (now() - '1 minute'::interval);

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

On Tuesday 14 December 2004 19:35, Steven Klassen wrote:
> # The schema for translog is as follows:
> #
> # CREATE TABLE translog (
> # id serial,
> # action integer,
> # sdate varchar(16) NOT NULL,
> # stime varchar(16) NOT NULL
> # );
>
> I assume you mean the composite sdate/stime is 1 minute ago?
>
> Instead of storing separate date & time varchar fields, why don't you
> store one timestamp field? Then a query like this would work:
>
> SELECT * FROM translog WHERE start <= (now() - '1 minute'::interval);
Thanx this works!

- Martin -