Thread: timestamp format bug
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
>>> 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
"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
> -----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
>>> 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)
> -----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
>>> 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
> -----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
>>> 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
>>> 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
> -----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