0

I am using symfony 3 and doctrine for my new project. When generating new entity what is best time format to select? datetime, datetimetz, date, time? My main activity with time will be used for logging events (modified_at, created_at and etc.) How to select the right one for MySQL database? I am planning to do time calculations in future. Any recommendations?

Aipo
  • 1,805
  • 3
  • 23
  • 48

1 Answers1

1

Depends on how you want to use timezone in your system. For example. Symfony has a terminology of :

  • Model Timezone : The timezone that associated with database and system.
  • View Timezone : The timezone that is used for end user. (Useful, for applications built for different timezone.)

The best options would be, to follow a standard to store all date_time in UTC timezone in your database which will not require to save a timezone along with the date_time.

When you fetch data for end user, you can always convert dates as per View Timezone.

Regarding your question (format of datetime):

If your data needs only date to deal with. You should use date field type. Else you can go with datetime or timestamp, if you are considering above options to manage timezone.

Else, if you have to store timezone along with your data, datetimetz might be a solution.

Jeet
  • 1,587
  • 1
  • 9
  • 22
  • My current timezone is UTC+2 (+3 summer time). I'll try datetime as default, but what are benefits of each (date, datetime, timestamp). When you would pick them to use for. – Aipo Aug 09 '16 at 10:39
  • `date` will not save time along with, which you need probably. so Its between `datetime` and `timestamp`. As you want `mysql` query with `date` calculation later. Its always good to go with `datetime` instead `timestamp`. check [this answer](http://stackoverflow.com/a/409305/1140358) for a brief explanation. – Jeet Aug 09 '16 at 10:50