Thread: Formatting TimeStamp
Hi, I am using the timestamp various tables but dont like to see the microseconds. Is there way to format the output of timestamp fields globally (in the postgres.conf)? Preferably i only would like to see the YYYYMMDD HH:MM:SS. Or can a formatting be passed to a SELECT * .... hmmm probably not. Thanks for any advise A
Alex wrote: > Hi, > > I am using the timestamp various tables but dont like to see the > microseconds. > Is there way to format the output of timestamp fields globally (in the > postgres.conf)? > Preferably i only would like to see the YYYYMMDD HH:MM:SS. > > Or can a formatting be passed to a SELECT * .... hmmm probably not. http://www.postgresql.org/docs/8.0/static/functions-datetime.html And yes you can do it in a SELECT. Sincerely, Joshua D. Drake > > Thanks for any advise > > A > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Joshua, I know that i can format it explicitly, but instead of using a SELECT * FROM .. I have to name the columns which is not very convenient. Right now I have created views for each table just to re-format the output of the one timestamp field. Mysql for example has a in addition to the TIMESTAMP also a DATETIME type that returns |'YYYY-MM-DD HH:MM:SS'. So, I was wondering if there is anything similar in postgres or can i set the precision or format of the timestamp in the postgres.conf or can I define a new type. Alex | Joshua D. Drake wrote: > Alex wrote: > >> Hi, >> >> I am using the timestamp various tables but dont like to see the >> microseconds. >> Is there way to format the output of timestamp fields globally (in >> the postgres.conf)? >> Preferably i only would like to see the YYYYMMDD HH:MM:SS. >> >> Or can a formatting be passed to a SELECT * .... hmmm probably not. > > > http://www.postgresql.org/docs/8.0/static/functions-datetime.html > > And yes you can do it in a SELECT. > > Sincerely, > > Joshua D. Drake > > > >> >> Thanks for any advise >> >> A >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > >
> Joshua, > I know that i can format it explicitly, but instead of using a SELECT * FROM > .. I have to name the columns which is not very convenient. Right now I have > created views for each table just to re-format the output of the one > timestamp field. Not sure it solves your problem, but you can do: SELECT *, TO_CHAR(...yourdatecol...) AS yourdatecol_fmtFROM ..... you still have to specify the one column, but not the others... just a thought. > > Mysql for example has a in addition to the TIMESTAMP also a DATETIME type > that returns |'YYYY-MM-DD HH:MM:SS'. So, I was wondering if there is anything > similar in postgres or can i set the precision or format of the timestamp in > the postgres.conf or can I define a new type. > > Alex > > | > Joshua D. Drake wrote: > >> Alex wrote: >> >>> Hi, >>> >>> I am using the timestamp various tables but dont like to see the >>> microseconds. >>> Is there way to format the output of timestamp fields globally (in the >>> postgres.conf)? >>> Preferably i only would like to see the YYYYMMDD HH:MM:SS. >>> >>> Or can a formatting be passed to a SELECT * .... hmmm probably not. >> >> >> http://www.postgresql.org/docs/8.0/static/functions-datetime.html >> >> And yes you can do it in a SELECT. >> >> Sincerely, >> >> Joshua D. Drake >> >> >> >>> >>> Thanks for any advise >>> >>> A >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 1: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that your >>> message can get through to the mailing list cleanly >> >> >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >