How to remove TimeZone in query to_timestamp Postgresql

Question

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?

Answer 1

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 ;

Answer 2

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.