to_timestamp busted?

Поиск
Список
Период
Сортировка
От Eliel Mamousette
Тема to_timestamp busted?
Дата
Msg-id 000001c0fd3c$9e5d65c0$2001a8c0@blockhead
обсуждение исходный текст
Ответы Re: to_timestamp busted?
Re: to_timestamp busted?
Re: to_timestamp busted?
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Paul
Дата:
Сообщение: Re[6]: Postgres is too slow?
Следующее
От: Karel Zak
Дата:
Сообщение: Re: to_timestamp busted?