1

I have downloaded a postgres database in .sql format. The type of temporal column in the database is: timestamp with time zone. And dates are mentioned in the database in the form given below:

 1866-05-17 05:53:28+05:53:28
 1901-09-28 05:53:20+05:53:20
 1960-01-01 05:30:00+05:30
 1944-07-21 06:30:00+06:30

Now I am unable to interpret as to what is meant by: e.g. "05:53:28+05:53:28". That is to which time zones do these times represent. And how should I convert these timestamps, such that a user may easily query to find out a persons date of birth without getting confused regarding the timezones.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rose Beck
  • 375
  • 1
  • 11

2 Answers2

4

In PostgreSQL you don't have to worry about the odd time zone offsets. Internally, for a timestamp with time zone (timestaptz for short) only the count of seconds since '1970-01-01' in UTC time zone is stored.

The same is true for timestamp in MySQL 5.0 and above:
Should I use field 'datetime' or 'timestamp'?

If you are only interested in the date, you may want to convert the column from timestamptz to date. In Postgres, when casting to date, the local time zone is applied. Try:

SELECT '2013-05-21 00:19:50+02'::timestamptz::date

The odd time zone offsets are due to historically diverse regimes like "mean solar time" before the early 20th century. We had a similar question recently, where I answered with an explanation:
PostgreSql: Getting strange-formated "timestamp with time zone"

More explanation for the Postgres data types timestamp and timestamptz:
Ignoring timezones altogether in Rails and PostgreSQL

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

"05:53:28+05:53:28" means the time 05:53:28 in a time zone that is 5 hours, 53 minutes and 28 seconds ahead of UTC. In other words, that time is midnight UTC. Note that there is no actual time zone that is offset from UTC by 5:53:28. Most time zones are offset from UTC by whole hours, a few by half hours, and even fewer (only a small hand full) by quarter hours.

In fact, every sample time you've provided is at midnight UTC, on different historical dates.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189