From: | "Eliel Mamousette" <eliel(at)panix(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | to_timestamp busted? |
Date: | 2001-06-25 06:03:55 |
Message-ID: | 000001c0fd3c$9e5d65c0$2001a8c0@blockhead |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have gotten some interesting results in testing out date
conversions using to_timestamp.
Given the query:
[NB: log_date and log_time are both varchar fields.
Yes I am yet another person parsing web server logs....]
SELECT timestamp(log_date || ' ' || log_time),
log_date,
log_time
FROM iis_log limit 10 ;
I get:
timestamp | log_date | log_time
------------------------+------------+----------
2001-06-20 00:05:54-04 | 2001-06-20 | 00:05:54
2001-06-23 00:01:45-04 | 2001-06-23 | 00:01:45
2001-06-20 23:58:45-04 | 2001-06-20 | 23:58:45
2001-06-23 00:01:48-04 | 2001-06-23 | 00:01:48
2001-06-20 23:59:03-04 | 2001-06-20 | 23:59:03
2001-06-23 00:02:42-04 | 2001-06-23 | 00:02:42
2001-06-20 00:05:46-04 | 2001-06-20 | 00:05:46
2001-06-23 00:02:48-04 | 2001-06-23 | 00:02:48
2001-06-20 23:59:03-04 | 2001-06-20 | 23:59:03
2001-06-23 00:03:15-04 | 2001-06-23 | 00:03:15
(10 rows)
which looks good to me.
But using:
SELECT to_timestamp(log_date || ' ' || log_time,
'YYYY-MM-DD HH24:MM:SS'),
log_date,
log_time
FROM iis_log limit 10;
I get the bizarre results of:
to_timestamp | log_date | log_time
------------------------+------------+----------
2001-05-20 00:00:54-04 | 2001-06-20 | 00:05:54
2001-01-23 00:00:45-05 | 2001-06-23 | 00:01:45
2005-10-20 23:00:45-04 | 2001-06-20 | 23:58:45
2001-01-23 00:00:48-05 | 2001-06-23 | 00:01:48
2005-11-20 23:00:03-05 | 2001-06-20 | 23:59:03
2001-02-23 00:00:42-05 | 2001-06-23 | 00:02:42
2001-05-20 00:00:46-04 | 2001-06-20 | 00:05:46
2001-02-23 00:00:48-05 | 2001-06-23 | 00:02:48
2005-11-20 23:00:03-05 | 2001-06-20 | 23:59:03
2001-03-23 00:00:15-05 | 2001-06-23 | 00:03:15
(10 rows)
Anyone seen this before?
thanks,
eliel
From | Date | Subject | |
---|---|---|---|
Next Message | Eric G. Miller | 2001-06-25 07:06:46 | Re: to_timestamp busted? |
Previous Message | Paul | 2001-06-25 04:02:47 | Re[6]: Postgres is too slow? |