Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E479B2E0069 for ; Wed, 30 Jan 2008 19:37:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76744-04-2 for ; Wed, 30 Jan 2008 19:37:05 -0400 (AST) Received: from outbound1.ucsd.edu (outbound1.ucsd.edu [132.239.1.205]) by postgresql.org (Postfix) with ESMTP id 9AFBF2E0122 for ; Wed, 30 Jan 2008 19:37:04 -0400 (AST) Received: from smtp.ucsd.edu (smtp.ucsd.edu [132.239.1.49]) by outbound1.ucsd.edu (8.13.6/8.13.6) with ESMTP id m0UNb2ES085138 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Wed, 30 Jan 2008 15:37:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; s=2007001; d=ucsd.edu; c=simple; q=dns; b=kCFAcJjPZcg71ngZO1UjW5zdkggmdfCJTvMNYQqkhFll4e+mh+hqhjQk6eSWFgpi4 4jztH9N1FpPEh5m2/STnw== Received: from [137.110.115.155] (dyn137-110-115-155.ucsd.edu [137.110.115.155]) (authenticated bits=0) by smtp.ucsd.edu (8.13.6/8.13.6) with ESMTP id m0UNb1v2028974 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 30 Jan 2008 15:37:02 -0800 (PST) X-Authentication-Warning: smtp.ucsd.edu: Host dyn137-110-115-155.ucsd.edu [137.110.115.155] claimed to be [137.110.115.155] Message-ID: <47A10A1D.5020408@ucsd.edu> Date: Wed, 30 Jan 2008 15:37:01 -0800 From: Luca Clementi User-Agent: Thunderbird 2.0.0.6 (X11/20071022) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Problem with the to_timestamp function Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/232 X-Sequence-Number: 30085 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