Breaking News: Grepper is joining You.com. Read the official announcement!

postgresql change timezone on timestamp without timezone

Muddy Moose answered on November 16, 2020 Popularity 10/10 Helpfulness 3/10

answer postgresql change timezone on timestamp without timezone

postgresql change timezone on timestamp without timezone

Comment

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

Popularity 10/10 Helpfulness 3/10 Language sql

Source: stackoverflow.com

Tags: postgresql sql timestamp timezone

Link to this answer
Share Copy Link

Contributed on Nov 03 2021

Muddy Moose

0 Answers Avg Quality 2/10

Closely Related Answers

postgres set timezone

Comment

SET TIME ZONE 'Europe/Moscow';

Popularity 10/10 Helpfulness 5/10 Language whatever

Source: Grepper

Tags: set

Link to this answer
Share Copy Link

Contributed on Nov 16 2020

Evolcon

0 Answers Avg Quality 2/10

timestamp with timezone postgres

Comment

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)

Popularity 10/10 Helpfulness 5/10 Language whatever

Source: stackoverflow.com

Tags: timestamp-with-timezone timestamp-with-t

Link to this answer
Share Copy Link

Contributed on Nov 05 2021

Nutty Nightingale

0 Answers Avg Quality 2/10

postgres insert timestamp without timezone

Comment

            
                
            
         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)

Popularity 10/10 Helpfulness 4/10 Language sql

Source: www.postgresqltutorial.com

Tags: insert in

Link to this answer
Share Copy Link

Contributed on Jun 10 2021

Determined Donkey

0 Answers Avg Quality 2/10

postgres insert timestamp without timezone

Comment

            
                
            
         CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);Code language: SQL (Structured Query Language) (sql)

Popularity 10/10 Helpfulness 2/10 Language sql

Source: www.postgresqltutorial.com

Tags: insert in

Link to this answer
Share Copy Link

Contributed on Jun 10 2021

Determined Donkey

0 Answers Avg Quality 2/10

postgresql change timezone on timestamp without timezone

Contents

More Related Answers

postgresql change timezone on timestamp without timezone

Closely Related Answers

postgres set timezone

timestamp with timezone postgres

postgres insert timestamp without timezone

postgres insert timestamp without timezone

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.