Skip site navigation (1) Skip section navigation (2)

Problem with the to_timestamp function

From: Luca Clementi <lclement(at)ucsd(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problem with the to_timestamp function
Date: 2008-01-30 23:37:01
Message-ID: 47A10A1D.5020408@ucsd.edu (view raw or flat)
Thread:
Lists: pgsql-sql


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



Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-01-31 00:26:10
Subject: Re: Problem with the to_timestamp function
Previous:From: Gregory StarkDate: 2008-01-30 20:24:09
Subject: Re: Sql ORDER BY and ASC/DESC question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group