From: Luca Clementi <lclement@ucsd.edu>
To: pgsql-sql@postgresql.org
Subject: Problem with the to_timestamp function
Date: Wed, 30 Jan 2008 15:37:01 -0800
Message-ID: <47A10A1D.5020408@ucsd.edu> (raw)
So the start_time is a varchar column, which contains a date.
opal_app=# select job_id,start_time from job_status where job_id='app1201551799779' ;
job_id | start_time
------------------+--------------------------
app1201551799779 | 1 28, 2008 12:23:19 午後
(1 row)
opal_app=# select job_id,to_timestamp(start_time, 'M DD, YYYY HH12:MI:SS 午後') from job_status where job_id='app1201551799779';
job_id | to_timestamp
------------------+------------------------
app1201551799779 | 2008-01-28 02:23:19-08
(1 row)
opal_app=# select job_id,to_timestamp(start_time, 'M DD, YYYY HH12:MI:SS') from job_status where job_id='app1201551799779';
job_id | to_timestamp
------------------+------------------------
app1201551799779 | 2008-01-28 02:23:19-08
(1 row)
opal_app=# select job_id,to_timestamp(start_time, 'M DD, YYYY HH:MI:SS') from job_status where job_id='app1201551799779';
job_id | to_timestamp
------------------+------------------------
app1201551799779 | 2008-01-28 02:23:19-08
(1 row)
It seems that the to_timestamp does not work properly in this case,
when it comes to parsing the hours. I verified that this problem
happen no matter what the input hours is and the result is always
00:min:sec or 02:min:sec.
opal_app=# select job_id,start_time from job_status where job_id='app1201563668439';
job_id | start_time
------------------+-------------------------
app1201563668439 | 1 28, 2008 3:41:08 午後
(1 row)
opal_app=# select job_id, to_timestamp(start_time, 'M DD, YYYY HH24:MI:SS') from job_status where job_id='app1201563668439';
job_id | to_timestamp
------------------+------------------------
app1201563668439 | 2008-01-28 00:01:08-08
(1 row)
Or
opal_app=# select job_id,start_time from job_status where job_id='app1201565220760';
job_id | start_time
------------------+-------------------------
app1201565220760 | 1 28, 2008 4:07:00 午後
(1 row)
opal_app=# select job_id,to_timestamp(start_time, 'M DD, YYYY HH:MI:SS') from job_status where job_id='app1201565220760';
job_id | to_timestamp
------------------+------------------------
app1201565220760 | 2008-01-28 00:07:00-08
(1 row)
Is this a bug or am I doing something wrong?
Thank you for any help,
Luca
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lclement@ucsd.edu
Subject: Re: Problem with the to_timestamp function
In-Reply-To: <47A10A1D.5020408@ucsd.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox