6

I have string containing an ISO 8601 formatted date-time value (ex: 2013-05-21T15:00:00+0200) and time zone (ex: Europe/Rome).

What is the best Postgres data type to represent this date format?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Safari
  • 11,437
  • 24
  • 91
  • 191
  • 3
    Just for future questions, please always mention your exact PostgreSQL version (`select version()`). It isn't important for this particular question, but it often will be. – Craig Ringer May 28 '13 at 00:33

1 Answers1

8

Type "timestamp with time zone" is what you want.

http://www.postgresql.org/docs/current/static/datatype-datetime.html

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • I have a string in this format: 2013-05-21T15:00:00+0200" and Timezone: "Europe/Rome" How can I use this string into a INSERT query if I use a data type as "timestamp with time zone"? Need I a particular function to conversion a ISO 8601 datetime into timestamp with time zone format? – Safari May 28 '13 at 07:30
  • Postgres doesn't store timezone, it just recalculate timestamp to server timezone. And your timezone is specify by "+200". But INSERT INTO tab(somecol) VALUES('2013-05-21T15:00:00+0200') should work – Pavel Stehule May 28 '13 at 10:56