Re: Query with time zone offset but without seconds

On 2011-03-25 scrawford@pinpointresearch.com (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