1

I have faced this problem in other projects, but right now we're working in Django / Python with Vue.js and PostgreSQL.

Nearly every 'date' field in our model is truly a date, not a datetime. The business users don't care about a time-part, in fact storing any such value is misleading. A good example is the effective date on a tax rate. From the user point of view, it takes effect at midnight on the specified date.

If we store this as a Django DateTimeField or DateField, it requires a time-part. It could be 0000h, but the value has no meaning to the business, because they will never need to see or compare anything with the time-part. The Django functionality is great if we want to store a real datetime, put it into the database as UTC, and then display it to users in their local time through the automatic time zone conversion. But we don't. If the effective date is March 1, 2019, it should display as such regardless of the user's timezone. If the date is stored as a datetime (2019, 3, 1, 0, 0, 0) and entered by someone in Vancouver, it will appear as the next calendar day for another user in Toronto. Definitely not what we want. We could kludge it by setting the time-part to 1200h, but really?

We also have potential problems, depending on the internal representation in the database, when using SQL or tools that access the schema directly (e.g. BI tools). How do we know what time zone applies to the datetime value?

So, we're thinking of using Django CharField with ISO 8601 format (YYYY-MM-DD) instead. It will sort properly, it can be compared easily (or directly in some tools like SQL), and can be displayed without reformatting if the client is willing to use the standard. If we need to do date arithmetic, we can use the Python Standard Libraries datetime and calendar to convert from/to string. We'll need to use those to catch SQL injection attacks anyway.

We will also need to deal with date entry through a Datepicker, converting to the ISO 8601 string before storing and back again when displaying for edit.

It appears to be a better way to represent what the business needs, and it gets rid of any timezone conversion issues.

There is certainly a lot of comment on datetime and time zone handling, but I haven't found anyone taking this approach to storing true dates. Am I missing an important 'gotcha'? We're early enough in the project that we can go either way, so I'm hoping to confirm that this will work before refactoring becomes a big job.

John R
  • 23
  • 6
  • *`"then display it to users in their local time through the automatic time zone conversion."`* This is inaccurate. Django doesn't do any automatic tz conversion. It only stores datetime in the database as UTC (or whatever tz you want to use) and it's up to you if you want to display that datetime in the original tz as it's stored in the database or do any conversions according to the end user. So, the effective date - *March 1, 2019* will not be auto converted. – xyres Jan 25 '19 at 09:28
  • I answered a question some time ago where I explained how timezone in django works, you may find it helpful:- https://stackoverflow.com/a/48028119/1925257. Also, there's a `DateField` which doesn't require time. – xyres Jan 25 '19 at 09:33

1 Answers1

0

Have you considered using DateField?

This will only store the date part and not the time.

Stepan Grigoryan
  • 3,062
  • 1
  • 17
  • 6
  • That's exactly it. I misunderstood the Django docs where it says that DateField is datetime.date - I read that as "it's stored as a datetime but only the datepart is returned". Further reading of the Python Standard Library 'datetime' docs made it clear that this is what I need. I also tested it by creating a DateField in a model, checking that it was added to my db as PSQL DATE type, inserting a value, changing my TZ so that I appeared to be in China (and NOW() returned a datetime that is tomorrow for me), and confirming that the DATE value is unchanged on retrieval. Thanks! – John R Jan 25 '19 at 19:15