-1

I often need to store a date in my database, my question is what is universally the best way to do this.

I have used several methods in the past, for example:

  • Storing dayOfMonth, month and year in separate columns
  • Using a timestamp
  • Using DATE/DATETIME
Decline
  • 284
  • 4
  • 14

2 Answers2

2

First of all that depends of course on the datatypes your DBMS supports. Does it have separate DATE and TIME types, is there a DATETIME and maybe a timestamp, possibly with and without a timezone?

If I want to store a date (like DATE '2022-02-21'), I'd use DATE. If I want to store a date and time (like TIMESTAMP '2022-02-21 15:16:17'), I would probably use DATETIME. And only if I needed more precision (fractions of a second) or a timezone would I use a TIMESTAMP.

By using the appropriate data type, I can use all date functions and date arithmetic available and have it guaranteed that no invalid data makes it into the database (such as February 30).

And then there are other cases. Say, I want to store birth dates including the birth time, but sometimes I don't know the time. Then I'd store date and time separately (in DATE and TIME columns) and make the date not nullable and the time nullable. Or if I want to store repeating calendar events (every February 3, every Monday and Tuesday, etc.), I will probably decide to have separate columns for year (INT), month (INT), day (INT), weekday (INT or VARCHAR). And some matching check constraits of course.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
1

I always save datetime in one field in ISO 8601 datetime format, always in UTC timezone YYYY-MM-DDThh:mm:ss. That way

  • you won't face problems with understanding this time for any timezone or transmitting it to another one.
  • everyone who has access to DB can do mind calculations to understand the time in his/her timezone, no local format misunderstandings
  • you won't update database structure in future if you understand that date only is not enough anymore

I didn't face any business needs or specific queries to work better with any another approach.

barbariania
  • 499
  • 8
  • 19