Horton

Thread

  1. to_timestamp busted?

    Eliel Mamousette <eliel@panix.com> — 2001-06-25T06:03:55Z

    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

  2. Re: to_timestamp busted?

    Eric G. Miller <egm2@jps.net> — 2001-06-25T07:06:46Z

    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>

  3. Re: to_timestamp busted?

    Karel Zak <zakkr@zf.jcu.cz> — 2001-06-25T08:25:10Z

    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

  4. RE: to_timestamp busted?

    Eliel Mamousette <eliel@panix.com> — 2001-06-25T08:53:53Z

    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
>

  5. Re: to_timestamp busted?

    Tod McQuillin <devin@spamcop.net> — 2001-06-26T08:48:52Z

    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