How to read timezone from 'timestamp with time zone' column?
NickName:Bartosz Bilicki Ask DateTime:2015-06-11T23:51:48

How to read timezone from 'timestamp with time zone' column?

I am unable to find a way to read timezone value in PostgreSQL column of type timestamp with time zone.

JDBC offers method java.sql.ResultSet#getTimestamp(int, java.util.Calendar) but I must provide my own calendar. I have see no way to obtain that calendar from timestamp field I am reading.

I am working on system that stores time data of multiple timezones. I need to save data with timezone information, and be able to read that timezone back from database.

Is it possible without hacks like

  • storing timezone value in another field
  • storing date as string, like 'Wed 17 Dec 07:37:16 1997 PST'

I am using JDBC 41 (JDBC4 Postgresql Driver, Version 9.4-1201), java 8.

Copyright Notice：Content Author:「Bartosz Bilicki」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/30785635/how-to-read-timezone-from-timestamp-with-time-zone-column

Answers
Gord Thompson 2015-06-11T18:32:29

The PostgreSQL documentation here says that:\n\n\n For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).\n\n\nSo there is no need to \"store the time zone [that corresponds to the timestamp value]\" per se; the timestamp with time zone values are always stored as UTC.\n\nAlso, there is no need to \"obtain the Calendar from the timestamp field\". The purpose of the Calendar is for you to define the particular timezone that you want to work with in your Java application.\n\nIn other words, timestamp with timezone does not store values from various timezones (e.g., some in EST, others in PST, etc.), it converts everything to UTC when the timestamp values are inserted.",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “How to read timezone from 'timestamp with time zone' column?” related questions
How to read timezone id from GET-TIMEZONE function response in powershell

I am new to powershell, so do not know what is the best way to read id of timezone from the GET-TIMEZONE function response in powershell. The function returns response in this format I want to rea...

Show Detail
Sequelize read dates in local timezone or given timezone

I have set the timezone option to my config. To read time in local timezone I use useUTC to false. This was working. Currently this ain't working anymore. Throwing an warning when project starts as...

Show Detail
php how to get timezone name from timezone_name_from_abbr for Asia/Colombo

I want to get the timezone name as Asia/Colombo (Sri Lanka timezone name) from the timezone_name_from_abbr() method in PHP. timezone_name_from_abbr('IST',19800); always returns Asia/Calcutta. How ...

Show Detail
How to read TIMESTAMPTZ column value in sesson timezone which is set by SET TIMEZONE query

I am trying to read a timestamptz data with java jdbc. When i set session timezone (connection timezone) on postgresql, i expect to read all timestamptz columns in given timezone of the session. W...

Show Detail
How to read datetime with timezone in pandas

I am trying to create a dataframe from csv, and its first column is like "2013-08-25T00:00:00-0400"; "2013-08-25T01:00:00-0400"; "2013-08-25T02:00:00-0400"; "2013-08-25T03:00:00-0400"; "

Show Detail
How to read timezone aware datetimes as a timezone naive local DatetimeIndex with read_csv in pandas?

When I use pandas read_csv to read a column with a timezone aware datetime (and specify this column to be the index), pandas converts it to a timezone naive utc DatetimeIndex. Data in Test.csv:

Show Detail
How to read timezone datetime objects

I have a timezone aware datetime object, which, if printed contains: 2013-03-04 18:00:00+01:00. But I always fail to understand how to read them. Does this refers to 19:00 local time or to 18:00 lo...

Show Detail
MySQL - read system timezone as the serverTimeZone

when I upgraded the mysql-connector-java from 5.1.35 to 8.0.21, we found some timezone issue. Mysql timezone setting: mysql> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; +--------------------...

Show Detail
Postgresql JDBC read data with timezone

I am using Postgresql JDBC in order to read data from another server which has different timezone with my client system. Reading data has some date type columns. I want these columns to become in ...

Show Detail
How to change timezone in read-only rootfs in Yocto poky warrior

I am trying to change the timezone on an embedded Linux (Yocto poky warrior) for Raspberry-pi Cm3. But I am unable to do so. I get an error message stating root@raspberrypi-cm3:~# timedatectl set-

Show Detail