Prefer DATETIME to DATE in the MySQL Database about bhima HOT 3 CLOSED

Agreed 👍.

@DedrickEnc @jniles do you think it's worthwhile budgeting ~half a day for
someone to specifically write/ perform tests (E2E/Integration/Unit) on
DATETIME objects vs. DATES and showing that we can have some guarantees
about dates never being misinterpreted between the client/ server/
database? This would mean that 2.X can have this as a guarantee. Unless
this has already been shown within BHIMA.

Excellent suggestion backed with references.

On Wednesday, January 27, 2016, Jonathan Niles [email protected]
wrote:

As part of the bhima 2.X release cycle, I propose we migrate as many
tables as possible to using DATETIME column types instead of DATE for
storing date datapoints. Under this direction, we will design our table
columns following these guidelines:

• DATE should never be used. It does not store any time information,
  and might result in incorrect dates conversions between JS and SQL.
• DATETIME should be used for every column that needs to store a JS
  Date() object. The SQL datatype does not convert records from their
  original timezone to UTC (like a TIMESTAMP does), preferring to store them
  in the time zone of the server.
• TIMESTAMP should be used for every column that needs to know the
  create/update time of the record. This should be seen as information about
  the MySQL row, not the entity the row represents. TIMESTAMPs may help with
  data migrations and analytical tools later on.

It is worth reading the page on MySQL dates [1]. Also useful is this [2]
Stack Overflow discussion (see top two answers).

[1] http://dev.mysql.com/doc/refman/5.7/en/datetime.html.
[2]
http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp
.

—
Reply to this email directly or view it on GitHub
#45.

from bhima.

We definitely need integration tests to support this proposition. I'm working on a few, updating @mbayopanda's employee API tests (which register a date of birth), as part of #24. That is probably not enough though.

Another interesting experiment would be to see how large DATEs are compared to DATETIMEs. While I would still recommend using DATETIMEs over DATEs, it would be valuable information to have.

from bhima.

Most, if not all, of our dates have been migrated. Closing this.

from bhima.