/SQL/Postgresql change timezone on timestamp without timezone

Postgresql change timezone on timestamp without timezone

Postgres set timezone

SET TIME ZONE 'Europe/Moscow';

Postgresql change timezone on timestamp without timezone

-- You have an issue where entries in the database are recorded in local time without the timezone
-- Postgresql assumes that these entries are in fact UTC
-- This corrects these entries by converting them to actual UTC
-- You might want to update the column (from timestamp) to a timestamptz, then perform the command below
UPDATE <table>
SET <timestamptz_field> = 
  (<timestamptz_field> AT TIME ZONE 'UTC') AT TIME ZONE '<correct_time_zone>';
  
-- e.g start_date_time before = '2021-07-29 18:15:16+00' (This is from a location at GMT+2)
-- start_date_time after = '2021-07-29 16:15:16+00' (This is the actual UTC timestamp)
UPDATE public.uut_result
	SET start_date_time= (start_date_time at time zone 'utc') at time zone 'Africa/Harare'

Source: stackoverflow.com

Postgres insert timestamp without timezone

            
                
            
         INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');Code language: SQL (Structured Query Language) (sql)

Source: postgresqltutorial.com

Timestamp with timezone postgres

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

Source: stackoverflow.com

Similar codes

0 vote277 views1 answer
[object Object]: BY KimbleOne__DeliveryGroup__c, CreatedDate ^ ERROR at Row:1:Column:142 field 'CreatedDate' can not be grouped in a query call
SQL
19 votes277 views6 answers
Sql drop table if exists
SQL
6 votes269 views1 answer
Postgres get size of database
SQL
0 vote264 views1 answer
Sql decode
SQL
14 votes268 views1 answer
Oracle new column
SQL
0 vote245 views1 answer
How to Group by and concatenate arrays with all columns in PostgreSQL
SQL
8 votes268 views1 answer
Unique vs primary key
SQL
1 vote289 views1 answer
Mysql text to decimal
SQL
0 vote264 views1 answer
How convert into in in sql query
SQL
0 vote289 views2 answers
Wordpress change url after migration mysql query
SQL