Re: timestamp format bug

> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
> Sent: Thursday, January 31, 2008 12:33 PM
> To: Roberts, Jon; pgsql-hackers(at)postgresql(dot)org
> Subject: RE: [HACKERS] timestamp format bug
>
> >>> On Thu, Jan 31, 2008 at 12:28 PM, in message
> <1A6E6D554222284AB25ABE3229A92762715525(at)nrtexcus702(dot)int(dot)asurion(dot)com>,
> "Roberts,
> Jon" <Jon(dot)Roberts(at)asurion(dot)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