From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Marco <netuse(at)lavabit(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query with time zone offset but without seconds |
Date: | 2011-03-25 18:08:45 |
Message-ID: | 4D8CDA2D.2000309@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | salah jubeh | 2011-03-25 18:30:51 | Re: which view is used another views |
Previous Message | Emre Hasegeli | 2011-03-25 17:25:13 | Re: which view is used another views |