0

I want to store datetime strings in my SQL database. And some of these may be timezone-aware. I have 3 ways of storing them:

  1. Storing them as a string without timezone, such as '1995-01-13 12:41:04.231132', and storing the timezone offset '+0000' in a separate column.

  2. Storing them as a string without timezone, such as '1995-01-13 12:41:04.231132', and storing the timezone name 'Africa/Cairo' in a separate column.

  3. Storing the entire thing as a string: '1995-01-13 12:41:04.231132+0000', with the offset attached.

What would be the ideal way to do this?

Note: I am using PostgreSQL database.

Extra details: I am in fact storing these datetimes in a JSONB column in my PostgreSQL database, so during queries and ordering, I need to cast these using the appropriate data type.

darkhorse
  • 8,192
  • 21
  • 72
  • 148
  • It really depends on your needs. Are these *timestamps* of past events? Or are you scheduling future or recurring events? Do you have various differentiated time zones? Or is all data from a single time zone? Is it enough to know the UTC equivalent, or do you need the original local time? See also [the timezone tag wiki](https://stackoverflow.com/tags/timezone/info) and the [best practices](https://stackoverflow.com/a/2532962/634824) list. – Matt Johnson-Pint Dec 29 '18 at 00:02

1 Answers1

3

None of the above, use a timestamp with time zone data type. It can handle timezone info as either an offset, timezone name, or abbreviation.

Sentinel
  • 6,379
  • 1
  • 18
  • 23
  • I added an extra note, but can you give me a format for this timestamp. I only see offsets being used in the official docs. – darkhorse Dec 28 '18 at 21:24
  • 1
    [Here](https://www.postgresql.org/docs/9.1/datatype-datetime.html#DATATYPE-TIMEZONE-TABLE)s the timezone table from the docs. and examples abound in the section on time just above this table as well as the timestamp section below – Sentinel Dec 28 '18 at 21:26
  • Thank you, now I get it. I added an edit to the question under extra note. Do you know if the date stored as a timestamp can be casted for ordering? I did not find it here: https://www.postgresql.org/docs/current/functions-json.html. – darkhorse Dec 28 '18 at 21:32
  • Timestamps can be ordered as is, no need to cast them to a different data type. That's one (only one) of the advantages of storing dates and times in their appropriate data types as opposed to storing them as strings, which is largely considered a BAD idea. – Sentinel Dec 28 '18 at 21:56
  • I specifically mentioned in the question that its stored in a JSONB column. – darkhorse Dec 28 '18 at 21:57
  • Note: `timestamp with time zone` does not actually *store* the time zone. It uses the time zone for conversion to/from UTC. If you have various different time zones, they will *not* be stored with this approach. A separate field is required for either the offset or the time zone ID (depending on your needs). (This is in [the docs](https://www.postgresql.org/docs/current/datatype-datetime.html), towards the bottom of section 8.5.1.3.) – Matt Johnson-Pint Dec 28 '18 at 23:58