On 2011-03-25 scrawford@xxxxxxxxxxxxxxxxxxxx (Steve Crawford) wrote: > On 03/25/2011 10:05 AM, Marco wrote: > > I have a column Âtimestamp with time zoneÂ. I want to extract the > > date/time in a different format including the time zone offset in a query > > but without seconds. If I do > > > > select to_char(datetime, 'YYYY-MM-DD HH24:MI') from table; > > > > then the time zone offset is missing in the output: 2011-03-25 18:01 > > If I do > > > > select date_trunc( 'minute', datetime) from table; > > > > then the time zone offset is present, but the seconds are not removed: > > 2011-03-25 18:01:00+01 > > > > I want it to look like this: 2011-03-25 18:01+01 > There are the TZ and tz formats but they return abbreviated names, not > offsets. It's a tiny kludge, but this should do what you want: > to_char(now(), 'YYYY-MM-DD HH:MM') || to_char(extract(timezone_hour from > now()), 'S09') Works fine. Thanks for the solution. > This is based on the assumption that you will never have to deal with > timezones that have other than whole-hour offsets: > select * from pg_timezone_names where utc_offset::text !~ '00:00'; If I move to Calcutta, I'll let you know and you can cook up a better solution ;) Regards Marco -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
