I'm finding in Postgresql and I want to remove +9 UTC value in my query.
For example: In to_timestamp column, I want to remove +09, and only keep 2016-02-26 00:23:44 value.
This is my query:
select t,to_timestamp(v) from c1.hour where f = 22 and date_trunc('day',t - '1 hour'::INTERVAL) = '2016-02-26 00:00:00' order by t ;
Here my result:
And this is my result when I didn't use to_timestamp:
Can I get some help on this please?
Use to_char() function to format timestamp to any desired string representation. Example:
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
will return 2016-02-26 09:37:49 (without a timezone)
Your query have to be this:
SELECT t, to_char(to_timestamp(v), 'YYYY-MM-DD HH24:MI:SS')
FROM c1.hour
WHERE
f = 22
AND date_trunc('day',t - '1 hour'::INTERVAL) = '2016-02-26 00:00:00'
ORDER BY t ;
Specify UTC:
select to_timestamp(0)::timestamptz at time zone 'UTC'
I am in timezone +08 and get the following result:
1970-01-01 00:00:00
With the time zone specification, I get:
1970-01-01 08:00:00+08
An advantage is you don't have to specify the date-time format, which can cause unexpected results for users. The system specified formatting is preserved.
1970-01-01 08:00:00 instead.
at time zone "UTC" will do that.at time zone "UTC" subtracts 8 hours as well, that seems to be not what OP wants, but just to remove +08 in your case while keeping the rest intact as if at your zone.You can do this:
cast("your column" as timestamp)
trunc('minute', cast (col_timestand_with_tz as timestamp))