Обсуждение: Auto-formatting timestamps?

Hi all,

I'm undergoing a port from an old Sybase database to Postgres. It's
going surprisingly well, but I have a question regarding formatting of
timestamps.

In Sybase, we get:
:> select date from checkoutdate;
date
'May  1 2001 12:00AM'
...

In Postgres:
=> select date from checkoutdate;      date
---------------------2001-05-01 00:00:00
...

I can properly format it using to_char:
=> select to_char(date, 'Mon DD YYYY HH:MIAM') as date from checkoutdate;      date
---------------------May 01 2001 12:00AM
...

Short of creating a wrapper type for timestamp (which seems like
overkill just for modifying the output function), is there a way to
output the Sybase format automatically (i.e. without a call to
to_char)?

I've found some code that actually somewhat depends on this format,
and one of my goals in this port is to change as little client code as
possible. Is it possible to automatically change the output like this,
preferably on a per-connection basis? I found stuff regarding the
datestyle parameter in the docs, but that doesn't quite do what I'd
like.

Thanks much,
Peter

Why not create a view?<br /><br /><br /><br /><div class="gmail_quote">On Tue, May 13, 2008 at 11:58 AM, Peter Koczan
<<ahref="mailto:pjkoczan@gmail.com">pjkoczan@gmail.com</a>> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi all,<br /><br />
I'mundergoing a port from an old Sybase database to Postgres. It's<br /> going surprisingly well, but I have a question
regardingformatting of<br /> timestamps.<br /><br /> In Sybase, we get:<br /> :> select date from checkoutdate;<br
/>date<br /> 'May  1 2001 12:00AM'<br /> ...<br /><br /> In Postgres:<br /> => select date from checkoutdate;<br />
     date<br /> ---------------------<br />  2001-05-01 00:00:00<br /> ...<br /><br /> I can properly format it using
to_char:<br/> => select to_char(date, 'Mon DD YYYY HH:MIAM') as date from checkoutdate;<br />       date<br />
---------------------<br/>  May 01 2001 12:00AM<br /> ...<br /><br /> Short of creating a wrapper type for timestamp
(whichseems like<br /> overkill just for modifying the output function), is there a way to<br /> output the Sybase
formatautomatically (i.e. without a call to<br /> to_char)?<br /><br /> I've found some code that actually somewhat
dependson this format,<br /> and one of my goals in this port is to change as little client code as<br /> possible. Is
itpossible to automatically change the output like this,<br /> preferably on a per-connection basis? I found stuff
regardingthe<br /> datestyle parameter in the docs, but that doesn't quite do what I'd<br /> like.<br /><br /> Thanks
much,<br/> Peter<br /><font color="#888888"><br /> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/> 

On Tue, May 13, 2008 at 5:54 PM, Mag Gam <magawake@gmail.com> wrote:
> Why not create a view?

That's a possibility, but the issue is that the output formatting is
bound to a data type (timestamp), not one or two particular tables.
Trying to create a view for all the tables with timestamps (and
creating appropriate rules for updating views) would be a huge
administrative PITA.

> > Hi all,
> >
> > I'm undergoing a port from an old Sybase database to Postgres. It's
> > going surprisingly well, but I have a question regarding formatting of
> > timestamps.
> >
> > In Sybase, we get:
> > :> select date from checkoutdate;
> > date
> > 'May  1 2001 12:00AM'
> > ...
> >
> > In Postgres:
> > => select date from checkoutdate;
> >       date
> > ---------------------
> >  2001-05-01 00:00:00
> > ...
> >
> > I can properly format it using to_char:
> > => select to_char(date, 'Mon DD YYYY HH:MIAM') as date from checkoutdate;
> >       date
> > ---------------------
> >  May 01 2001 12:00AM
> > ...
> >
> > Short of creating a wrapper type for timestamp (which seems like
> > overkill just for modifying the output function), is there a way to
> > output the Sybase format automatically (i.e. without a call to
> > to_char)?
> >
> > I've found some code that actually somewhat depends on this format,
> > and one of my goals in this port is to change as little client code as
> > possible. Is it possible to automatically change the output like this,
> > preferably on a per-connection basis? I found stuff regarding the
> > datestyle parameter in the docs, but that doesn't quite do what I'd
> > like.
> >
> > Thanks much,
> > Peter

Peter Koczan escribió:
> On Tue, May 13, 2008 at 5:54 PM, Mag Gam <magawake@gmail.com> wrote:
> > Why not create a view?
> 
> That's a possibility, but the issue is that the output formatting is
> bound to a data type (timestamp), not one or two particular tables.
> Trying to create a view for all the tables with timestamps (and
> creating appropriate rules for updating views) would be a huge
> administrative PITA.

If you're really set about that, you can add a new DateStyle setting.
It's a bit of C hacking.  (Or you can hire someone to do it for you.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support