[HACKERS] timestamp format bug
select to_char(date, '-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 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] timestamp format bug
On Thu, Jan 31, 2008 at 9:34 AM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: select to_char(date, '-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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp format bug
Kevin Grittner [EMAIL PROTECTED] writes: On Thu, Jan 31, 2008 at 9:34 AM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] timestamp format bug
-Original Message- From: Kevin Grittner [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: select to_char(date, '-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 -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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp format bug
On Thu, Jan 31, 2008 at 12:34 PM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] 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, '-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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] timestamp format bug
On Thu, Jan 31, 2008 at 12:28 PM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: The default timestamp format appears to be -mm-dd hh24:mi:ss.ms Not to me: select now(); now --- 2008-01-31 12:31:40.568746-06 (1 row) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp format bug
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] writes: On Thu, Jan 31, 2008 at 9:34 AM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp format bug
-Original Message- From: Kevin Grittner [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: The default timestamp format appears to be -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 -mm-dd hh24:mi:ss.us. So on your db, run this query: select sub.t1, to_char(t1, '-mm-dd hh24:mi:ss.us') as char_t1 from ( select timestamp'2008-01-31 12:31:40.50' as t1 ) sub I bet you get this: 2008-01-31 12:31:40.50;2008-01-31 12:31:40.50 Don't you think it should have two identical columns? Secondly, this link shows that ms should be 000-999 and us should be 00-99. 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp format bug
On Thu, Jan 31, 2008 at 12:45 PM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: So on your db, run this query: select sub.t1, to_char(t1, '-mm-dd hh24:mi:ss.us') as char_t1 from ( select timestamp'2008-01-31 12:31:40.50' as t1 ) sub I bet you get this: 2008-01-31 12:31:40.50;2008-01-31 12:31:40.50 t1 | char_t1 + 2008-01-31 12:31:40.50 | 2008-01-31 12:31:40.50 (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 00-99. Yes. That seems to me to work fine, as your examples show. -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] timestamp format bug
On Thu, Jan 31, 2008 at 12:34 PM, in message [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] 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.50' to_char turns that into a string of your chosen format. I hope that helps. -Kevin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] timestamp format bug
-Original Message- From: Kevin Grittner [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED], Roberts, Jon [EMAIL PROTECTED] wrote: So on your db, run this query: select sub.t1, to_char(t1, '-mm-dd hh24:mi:ss.us') as char_t1 from ( select timestamp'2008-01-31 12:31:40.50' as t1 ) sub I bet you get this: 2008-01-31 12:31:40.50;2008-01-31 12:31:40.50 t1 | char_t1 + 2008-01-31 12:31:40.50 | 2008-01-31 12:31:40.50 (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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
