pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with the to_timestamp function
7+ messages / 3 participants
[nested] [flat]
* Problem with the to_timestamp function
@ 2008-01-30 23:37 Luca Clementi <lclement@ucsd.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Luca Clementi @ 2008-01-30 23:37 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 00:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luca Clementi <lclement@ucsd.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2008-01-31 00:26 UTC (permalink / raw)
To: Luca Clementi <lclement@ucsd.edu>; +Cc: pgsql-sql
Luca Clementi <lclement@ucsd.edu> writes:
> It seems that the to_timestamp does not work properly in this case,
> when it comes to parsing the hours.
to_timestamp() is not very robust if the input doesn't exactly match
what it expects for the format string. I'm not sure if that's the
issue here, but have you tried just casting the string to timestamp?
That would use the standard timestamp input converter, which is
pretty flexible.
(BTW, there is definitely 0 hope of recognizing a timezone name that's
written in Chinese characters, unless maybe you fool around with the
timezone-abbreviations configuration file.)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 01:19 Michael Glaesemann <grzm@seespotcode.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Glaesemann @ 2008-01-31 01:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Luca Clementi <lclement@ucsd.edu>; pgsql-sql
On Jan 30, 2008, at 19:26 , Tom Lane wrote:
> (BTW, there is definitely 0 hope of recognizing a timezone name that's
> written in Chinese characters, unless maybe you fool around with the
> timezone-abbreviations configuration file.)
Just FYI, the characters in the original email are the equivalent of
PM, rather than designating a time zone. Not that that affects your
advice, of course.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 02:04 Luca Clementi <lclement@ucsd.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Luca Clementi @ 2008-01-31 02:04 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Luca Clementi <lclement@ucsd.edu> writes:
>> It seems that the to_timestamp does not work properly in this case,
>> when it comes to parsing the hours.
>
> to_timestamp() is not very robust if the input doesn't exactly match
> what it expects for the format string. I'm not sure if that's the
> issue here, but have you tried just casting the string to timestamp?
> That would use the standard timestamp input converter, which is
> pretty flexible.
>
> (BTW, there is definitely 0 hope of recognizing a timezone name that's
> written in Chinese characters, unless maybe you fool around with the
> timezone-abbreviations configuration file.)
>
I found the problem!!
to_timestamp(start_time, 'M DD, YYYY HH12:MI:SS')
^^^^^^
in the formatting string I have two spaces, while in the original there
is only one:
1 28, 2008 12:23:19 午後
So if I use: 'M DD, YYYY HH12:MI:SS' as a formatting string it works!
I wander how come if the formatting string doesn't match properly you
get such a bad behavior, would it better just a error?
PS: some e-mail clients do not render properly the spacing in my first
e-mail.
Thanks again for your help!!
Luca
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 06:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luca Clementi <lclement@ucsd.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2008-01-31 06:04 UTC (permalink / raw)
To: Luca Clementi <lclement@ucsd.edu>; +Cc: pgsql-sql
Luca Clementi <lclement@ucsd.edu> writes:
> I found the problem!!
> to_timestamp(start_time, 'M DD, YYYY HH12:MI:SS')
> in the formatting string I have two spaces, while in the original there
> is only one:
> 1 28, 2008 12:23:19 午後
> So if I use: 'M DD, YYYY HH12:MI:SS' as a formatting string it works!
> I wander how come if the formatting string doesn't match properly you
> get such a bad behavior, would it better just a error?
Yeah, it should either do something reasonable or throw an error.
Improving to_timestamp's handling of such cases is on the TODO list.
In the meantime, though, the standard input converter is a whole lot
more flexible for slightly-out-of-spec input ... so I ask again,
do you really need to_timestamp at all?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 18:00 Luca Clementi <lclement@ucsd.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Luca Clementi @ 2008-01-31 18:00 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Luca Clementi <lclement@ucsd.edu> writes:
>> I found the problem!!
>> to_timestamp(start_time, 'M DD, YYYY HH12:MI:SS')
>> in the formatting string I have two spaces, while in the original there
>> is only one:
>> 1 28, 2008 12:23:19 åˆå¾Œ
>> So if I use: 'M DD, YYYY HH12:MI:SS' as a formatting string it works!
>> I wander how come if the formatting string doesn't match properly you
>> get such a bad behavior, would it better just a error?
>
> Yeah, it should either do something reasonable or throw an error.
> Improving to_timestamp's handling of such cases is on the TODO list.
> In the meantime, though, the standard input converter is a whole lot
> more flexible for slightly-out-of-spec input ... so I ask again,
> do you really need to_timestamp at all?
What do you mean with th standard input converter, you mean casting that
string into a date with the default locate format. This won't work,
because the machine where the DB is running is set to en.US, so it won't
be able to parse that date. That's why I am using the to_timestamp.
I am doning some data clean up in my DB.
Thanks again for your help,
Luca
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with the to_timestamp function
@ 2008-01-31 18:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luca Clementi <lclement@ucsd.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2008-01-31 18:09 UTC (permalink / raw)
To: Luca Clementi <lclement@ucsd.edu>; +Cc: pgsql-sql
Luca Clementi <lclement@ucsd.edu> writes:
> What do you mean with th standard input converter, you mean casting that
> string into a date with the default locate format. This won't work,
> because the machine where the DB is running is set to en.US, so it won't
> be able to parse that date. That's why I am using the to_timestamp.
Locale has nothing to do with this --- at most you might want to adjust
the datestyle parameter.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-01-31 18:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-30 23:37 Problem with the to_timestamp function Luca Clementi <lclement@ucsd.edu>
2008-01-31 00:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-31 01:19 ` Michael Glaesemann <grzm@seespotcode.net>
2008-01-31 02:04 ` Luca Clementi <lclement@ucsd.edu>
2008-01-31 06:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-31 18:00 ` Luca Clementi <lclement@ucsd.edu>
2008-01-31 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
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