Thread: timestamp format bug

timestamp format bug

From
"Roberts, Jon"
Date:
select to_char(date, 'yyyy-mm-dd hh24:mi:ss.ms') as char,       date  from (select timestamp'2008-01-30 15:06:21.560'
asdate) sub 


"2008-01-30 15:06:21.560";"2008-01-30 15:06:21.56"

Why does the timestamp field truncate the 0 but when I show the
timestamp as a character in the default timestamp format, it does not
truncate the trailing zero?

These two fields should be consistent because they should be formatted
the same way.  I'm using versions 8.2.4 and 8.2.5 and both versions gave
me the same results.


Jon



Re: timestamp format bug

From
"Kevin Grittner"
Date:
>>> On Thu, Jan 31, 2008 at  9:34 AM, in message
<1A6E6D554222284AB25ABE3229A92762715521@nrtexcus702.int.asurion.com>, "Roberts,
Jon" <Jon.Roberts@asurion.com> wrote:
> select to_char(date, 'yyyy-mm-dd hh24:mi:ss.ms') as char,
>        date
>   from (select timestamp'2008-01-30 15:06:21.560' as date) sub
>
> "2008-01-30 15:06:21.560";"2008-01-30 15:06:21.56"
>
> These two fields should be consistent because they should be formatted
> the same way.
Why would you think that?
I would expect the timestamp to be presented with one to nine
digits to the right of the decimal point, depending on the value.
I can think of a couple database products which only go to three
decimal positions, and always show three, but that's hardly a
standard.
-Kevin




Re: timestamp format bug

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On Thu, Jan 31, 2008 at  9:34 AM, in message
> <1A6E6D554222284AB25ABE3229A92762715521@nrtexcus702.int.asurion.com>, "Roberts,
> Jon" <Jon.Roberts@asurion.com> wrote: 
>> These two fields should be consistent because they should be formatted
>> the same way.
> Why would you think that?

Indeed the whole *point* of to_char() is to display the value in a
different format than the type's standard output converter would use.

I think it'd be a reasonable complaint that to_char() offers no way
to control how many fractional-second digits you get in its output;
but that's a missing feature not a bug.

> I can think of a couple database products which only go to three
> decimal positions, and always show three, but that's hardly a
> standard.

Considering that to_char() is intended to be compatible with *r*cl*e,
if that's what they do then we may be stuck with doing the same.
        regards, tom lane


Re: timestamp format bug

From
"Roberts, Jon"
Date:
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Thursday, January 31, 2008 9:48 AM
> To: Roberts, Jon; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] timestamp format bug
> 
> >>> On Thu, Jan 31, 2008 at  9:34 AM, in message
> <1A6E6D554222284AB25ABE3229A92762715521@nrtexcus702.int.asurion.com>,
> "Roberts,
> Jon" <Jon.Roberts@asurion.com> wrote:
> > select to_char(date, 'yyyy-mm-dd hh24:mi:ss.ms') as char,
> >        date
> >   from (select timestamp'2008-01-30 15:06:21.560' as date) sub
> >
> > "2008-01-30 15:06:21.560";"2008-01-30 15:06:21.56"
> >
> > These two fields should be consistent because they should be formatted
> > the same way.
> 
> Why would you think that?
> 
> I would expect the timestamp to be presented with one to nine
> digits to the right of the decimal point, depending on the value.

I expect the query to return either:
"2008-01-30 15:06:21.560";"2008-01-30 15:06:21.560"

or:
"2008-01-30 15:06:21.56";"2008-01-30 15:06:21.56"

The default timestamp format appears to be yyyy-mm-dd hh24:mi:ss.ms but it
doesn't follow this for milliseconds.  It truncates the trailing zero for
timestamps and it does not truncate the trailing zero when cast as a
character.  

I don't care which standard should be adopted but it should be the same.

> 
> I can think of a couple database products which only go to three
> decimal positions, and always show three, but that's hardly a
> standard.


Oracle and MS SQL Server are consistent in this.



Jon


Re: timestamp format bug

From
"Kevin Grittner"
Date:
>>> On Thu, Jan 31, 2008 at 12:28 PM, in message
<1A6E6D554222284AB25ABE3229A92762715525@nrtexcus702.int.asurion.com>, "Roberts,
Jon" <Jon.Roberts@asurion.com> wrote:
> The default timestamp format appears to be yyyy-mm-dd hh24:mi:ss.ms
Not to me:
select now();             now
-------------------------------2008-01-31 12:31:40.568746-06
(1 row)




Re: timestamp format bug

From
"Roberts, Jon"
Date:
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, January 31, 2008 10:48 AM
> To: Kevin Grittner
> Cc: Roberts, Jon; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] timestamp format bug
>
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > On Thu, Jan 31, 2008 at  9:34 AM, in message
> >
<1A6E6D554222284AB25ABE3229A92762715521@nrtexcus702.int.asurion.com>,
> "Roberts,
> > Jon" <Jon.Roberts@asurion.com> wrote:
> >> These two fields should be consistent because they should be
formatted
> >> the same way.
>
> > Why would you think that?
>
> Indeed the whole *point* of to_char() is to display the value in a
> different format than the type's standard output converter would use.
>
> I think it'd be a reasonable complaint that to_char() offers no way
> to control how many fractional-second digits you get in its output;
> but that's a missing feature not a bug.
>
> > I can think of a couple database products which only go to three
> > decimal positions, and always show three, but that's hardly a
> > standard.
>
> Considering that to_char() is intended to be compatible with *r*cl*e,
> if that's what they do then we may be stuck with doing the same.
>


No, Larry's company doesn't round the zeros off for timestamp or date
data types and not round off the zeros for character conversions.  That
vendor leaves the trailing zeros for both.

If not to_char, what is the preferred method to convert a timestamp to a
string?



Jon


Re: timestamp format bug

From
"Kevin Grittner"
Date:
>>> On Thu, Jan 31, 2008 at 12:34 PM, in message
<1A6E6D554222284AB25ABE3229A92762715526@nrtexcus702.int.asurion.com>, "Roberts,
Jon" <Jon.Roberts@asurion.com> wrote:
> If not to_char, what is the preferred method to convert a timestamp to a
> string?
Your original post showed to_char apparently doing what you wanted, no?
> select to_char(date, 'yyyy-mm-dd hh24:mi:ss.ms') as char,
>        date
>   from (select timestamp'2008-01-30 15:06:21.560' as date) sub
>
>
> "2008-01-30 15:06:21.560";"2008-01-30 15:06:21.56"
The to_char function returned the timestamp as a character string with
three decimal positions.
-Kevin




Re: timestamp format bug

From
"Roberts, Jon"
Date:
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Thursday, January 31, 2008 12:33 PM
> To: Roberts, Jon; pgsql-hackers@postgresql.org
> Subject: RE: [HACKERS] timestamp format bug
>
> >>> On Thu, Jan 31, 2008 at 12:28 PM, in message
> <1A6E6D554222284AB25ABE3229A92762715525@nrtexcus702.int.asurion.com>,
> "Roberts,
> Jon" <Jon.Roberts@asurion.com> wrote:
> > The default timestamp format appears to be yyyy-mm-dd hh24:mi:ss.ms
>
> Not to me:
>
> select now();
>               now
> -------------------------------
>  2008-01-31 12:31:40.568746-06
> (1 row)
>

I'm guessing that is a server setting on how to format a timestamp.
Your appears to be yyyy-mm-dd hh24:mi:ss.us.


So on your db, run this query:
select sub.t1, to_char(t1, 'yyyy-mm-dd hh24:mi:ss.us') as char_t1
from
(
select timestamp'2008-01-31 12:31:40.500000' as t1
) sub


I bet you get this:
"2008-01-31 12:31:40.50";"2008-01-31 12:31:40.500000"

Don't you think it should have two identical columns?

Secondly, this link shows that ms should be 000-999 and us should be
000000-999999.

http://www.postgresql.org/docs/8.2/static/functions-formatting.html


All of the other fields are padded like month, day, year, hour, minute,
and second and are consistent.  The formats ms and us should be
consistent too.



Jon


Re: timestamp format bug

From
"Kevin Grittner"
Date:
>>> On Thu, Jan 31, 2008 at 12:45 PM, in message
<1A6E6D554222284AB25ABE3229A92762715527@nrtexcus702.int.asurion.com>, "Roberts,
Jon" <Jon.Roberts@asurion.com> wrote:

> So on your db, run this query:
> select sub.t1, to_char(t1, 'yyyy-mm-dd hh24:mi:ss.us') as char_t1
> from
> (
> select timestamp'2008-01-31 12:31:40.500000' as t1
> ) sub
>
>
> I bet you get this:
> "2008-01-31 12:31:40.50";"2008-01-31 12:31:40.500000"          t1           |          char_t1
------------------------+----------------------------2008-01-31 12:31:40.50 | 2008-01-31 12:31:40.500000
(1 row)
> Don't you think it should have two identical columns?
No.  Why should the return value of a function influence the input?
What would you expect from this query?:
select sub.t1, substring(sub.t1 from 2 for 3) as substring_t1
from
(
select 'abcde'::text as t1
) sub
> Secondly, this link shows that ms should be 000-999 and us should be
> 000000-999999.
Yes.  That seems to me to work fine, as your examples show.
-Kevin




Re: timestamp format bug

From
"Kevin Grittner"
Date:
>>> On Thu, Jan 31, 2008 at 12:34 PM, in message
<1A6E6D554222284AB25ABE3229A92762715526@nrtexcus702.int.asurion.com>, "Roberts,
Jon" <Jon.Roberts@asurion.com> wrote:
> doesn't round the zeros off for timestamp
Sorry to have been so slow, but I think this is the crux of it:
A timestamp represents a moment in time, without storing any
precision information.  These timestamp literals represent exactly
the same moment, and therefore have exactly the same internal
representation:
timestamp '2008-01-31 12:31:40.50'
timestamp '2008-01-31 12:31:40.500'
timestamp '2008-01-31 12:31:40.500000'
to_char turns that into a string of your chosen format.
I hope that helps.
-Kevin




Re: timestamp format bug

From
"Roberts, Jon"
Date:
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Thursday, January 31, 2008 1:47 PM
> To: Roberts, Jon; pgsql-hackers@postgresql.org
> Subject: RE: [HACKERS] timestamp format bug
>
> >>> On Thu, Jan 31, 2008 at 12:45 PM, in message
> <1A6E6D554222284AB25ABE3229A92762715527@nrtexcus702.int.asurion.com>,
> "Roberts,
> Jon" <Jon.Roberts@asurion.com> wrote:
>
> > So on your db, run this query:
> > select sub.t1, to_char(t1, 'yyyy-mm-dd hh24:mi:ss.us') as char_t1
> > from
> > (
> > select timestamp'2008-01-31 12:31:40.500000' as t1
> > ) sub
> >
> >
> > I bet you get this:
> > "2008-01-31 12:31:40.50";"2008-01-31 12:31:40.500000"
>
>            t1           |          char_t1
> ------------------------+----------------------------
>  2008-01-31 12:31:40.50 | 2008-01-31 12:31:40.500000
> (1 row)
>
> > Don't you think it should have two identical columns?
>
> No.  Why should the return value of a function influence the input?
>


This is clearly a bug.  Don't fix it.  I don't care.


Jon