Обсуждение: Query with time zone offset but without seconds

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

How to do that?


Marco

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
>
> How to do that?
>
>
> Marco
>
>

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')

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';

Cheers,
Steve

On Friday, March 25, 2011 10:05:59 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
>
> How to do that?
>
>
> Marco

Maybe something like:

test(5432)aklaver=>SELECT * from timestamp_test ;
 id | txt_fld |            ts_fld             |        ts_fld2
----+---------+-------------------------------+------------------------
  1 | test1   | 2009-03-11 12:35:43.065678-07 | (NULL)
  3 | test3   | 2009-03-11 13:37:01.166354-07 | (NULL)
  2 | test2   | 2009-03-11 12:42:15.276405-07 | (NULL)
  4 | test3   | 2009-03-11 14:37:48.993075-07 | (NULL)
  5 | test3   | 2009-03-12 12:38:07.722856-07 | (NULL)
  6 | test4   | 2009-12-24 13:35:51.59005-08  | (NULL)
  7 | test4   | 2009-12-24 13:37:32.499764-08 | 2009-12-24 13:37:32-08
  8 | t       | 2010-05-20 08:13:28.157027-07 | 2010-05-20 12:13:28-07
  9 | t       | 2010-05-20 08:13:43.265383-07 | 2010-05-20 10:13:43-07
 10 | t       | 2010-05-20 08:13:53.718519-07 | 2010-05-20 13:13:54-07
 11 | s       | 2011-03-25 09:00:00.124-07    | 2011-03-25 14:15:13-07
 12 | s       | 2011-03-25 09:12:00.124-07    | 2011-03-25 14:16:27-07


test(5432)aklaver=>SELECT regexp_replace(date_trunc('minute',
ts_fld)::text,':00{1}','') from timestamp_test;
   regexp_replace
---------------------
 2009-03-11 12:35-07
 2009-03-11 13:37-07
 2009-03-11 12:42-07
 2009-03-11 14:37-07
 2009-03-12 12:38-07
 2009-12-24 13:35-08
 2009-12-24 13:37-08
 2010-05-20 08:13-07
 2010-05-20 08:13-07
 2010-05-20 08:13-07
 2011-03-25 09:00-07
 2011-03-25 09:12-07



--
Adrian Klaver
adrian.klaver@gmail.com

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