1

I'm working on a webapp that has a Javascript front-end that talks JSON with a python (flask) middle and a postgres backend. I'd like to safely represent timestamps without loss, ambiguity or bugs. Python itself doesn't generate any timestamps, but it translates them between the client and the DB, which is where bugs can happen.

Javascript' lack of long means that sensible long-count-since-epoch-everywhere is lossy when stored as Javascript's number, and so iso datetime strings are the most least-unnatural format between the client and server. For example, from python, we can generate:

>>> datetime.fromtimestamp(time.time(), pytz.utc).isoformat()
'2018-02-08T05:42:48.866188+00:00'

Which can be unambiguously interpreted in the whole stack without loss of precision, whether or not the timezone offset is non-zero (as it may be coming from the client).

However, between python and the database, things get a little tricky. I'm concerned with preventing timezone unaware datetimes creeping into Python-land and then into the database. For example, a client in China may send JSON to the flask server in California with a string: '2018-02-07T21:46:33.250477' ... which we many parse as a timezone-unaware ISO datetime. Because this is an ambiguous time, the ONLY sensible thing to do it to reject it as an error. But where? I could manually write validation for each field received to Python, but with a large datamodel, it's easy to miss a field.

As I understand it, at the DB-schema level, it doesn't matter too much whether columns are declared timestamp or timestampz provided the queries always (ALWAYS) come with TZ information, they're unambiguously converted UTC for both types. However, as far as I know I don't think its possible for postgres to prevent you putting a timezone-less datetime or time-string into timestampz columns.

Two possibilities come to mind:

  • Could the flask JSON parser reliably detect iso dates without timezones and reject them?
  • I could leave the timestamps strings all the way to the psycopg2 cursor.execute. Can psycopg2's sql-formatter reject timestamp strings that don't have a timestamp?
user48956
  • 14,850
  • 19
  • 93
  • 154
  • Do you have control over the front end? I am thinking datetimes from the front end having a timezone can solve this issue - https://stackoverflow.com/questions/10087819/convert-date-to-another-timezone-in-javascript – Vivek Kalyanarangan Feb 08 '18 at 07:10
  • We’re providing one front end, but also an api that others can call. – user48956 Feb 08 '18 at 15:03

0 Answers0