Обсуждение: to_timestamp busted?
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
On Mon, Jun 25, 2001 at 02:03:55AM -0400, Eliel Mamousette wrote:
> SELECT to_timestamp(log_date || ' ' || log_time,
> 'YYYY-MM-DD HH24:MM:SS'),
^^
MM = month number
MI = minutes
test=# select to_timestamp('2001-06-20 00:05:54', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
------------------------
2001-06-20 00:05:54+02
(1 row)
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Mon, Jun 25, 2001 at 02:03:55AM -0400, Eliel Mamousette wrote: > 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....] > [snip] > But using: > > SELECT to_timestamp(log_date || ' ' || log_time, > 'YYYY-MM-DD HH24:MM:SS'), Take a hard look at your format specification. Hmm, months and minutes are the same. Maybe you want "MI" for minutes? -- Eric G. Miller <egm2@jps.net>
Drat! User error strikes again.
Thanks for the fast eyes on this Karel!
-eliel
> -----Original Message-----
> From: Karel Zak [mailto:zakkr@zf.jcu.cz]
> Sent: Monday, June 25, 2001 4:25 AM
> To: Eliel Mamousette
> Cc: pgsql-general@postgresql.org
> Subject: Re: to_timestamp busted?
>
>
> On Mon, Jun 25, 2001 at 02:03:55AM -0400, Eliel Mamousette wrote:
>
> > SELECT to_timestamp(log_date || ' ' || log_time,
> > 'YYYY-MM-DD HH24:MM:SS'),
> ^^
> MM = month number
> MI = minutes
>
> test=# select to_timestamp('2001-06-20 00:05:54', 'YYYY-MM-DD
> HH24:MI:SS');
> to_timestamp
> ------------------------
> 2001-06-20 00:05:54+02
> (1 row)
>
> Karel
>
>
> --
> Karel Zak <zakkr@zf.jcu.cz>
> http://home.zf.jcu.cz/~zakkr/
>
> C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
>
On Mon, 25 Jun 2001, Eliel Mamousette wrote: > But using: > > SELECT to_timestamp(log_date || ' ' || log_time, > 'YYYY-MM-DD HH24:MM:SS'), > log_date, > log_time > FROM iis_log limit 10; Hold on... you said MM twice -- once in the date and once in the time. I don't expect you have months in your time, do you? Try 'YYYY-MM-DD HH24:MI:SS' -- Tod McQuillin