Thread: 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 -