Value Of Datetime In Database

[eldzee]

Hello,

I'm not sure whether this is working as designed or something else.

 

I noticed that the value of datetime field in the database are all in UTC thus causing whenever I do direct analysis to the database from external application, the time values are shifted.

e.g. currently both my php and database timezones are set in: UTC+0700,

and now is: [2016-04-16 13:00:00], but whenever I add someting (client or notes), the value in the database for date_added became [2016-04-16 06:00:00]

[Michael]

The database is set in UTC so it won't match your settings / PHP etc.

[activa]

Hello,

I'm not sure whether this is working as designed or something else.

 

I noticed that the value of datetime field in the database are all in UTC thus causing whenever I do direct analysis to the database from external application, the time values are shifted.

e.g. currently both my php and database timezones are set in: UTC+0700,

and now is: [2016-04-16 13:00:00], but whenever I add someting (client or notes), the value in the database for date_added became [2016-04-16 06:00:00]

 

 

this behavior has affected another user 'naja7host' as i have read from some post earlier about suspension and due date ....

 

i'm not a professional or developer to talk about advantages/disadvantages of saving date/time in different  date/time of actual setting .

[Paul]

The system should be converting from UTC as stored in the database for display and other actions. If you find areas where this is not happening, please open a bug report.

 

In terms of utilizing data from the database in another application, you will need to adjust the date times from UTC to your desired timezone.

[eldzee]

The system should be converting from UTC as stored in the database for display and other actions. If you find areas where this is not happening, please open a bug report.

 

In terms of utilizing data from the database in another application, you will need to adjust the date times from UTC to your desired timezone.

 

I see, so blesta took care of the conversion.

 

But is there any reason not to change the table column type into TIMESTAMP instead of DATETIME (for those transactional type date), and then also open the PDO connection with SET time_zone to UTC query;

this way, the functionality of the date record stays the same, but it'll make easier for database admin to view the data without requiring the date conversion.

[Paul]

I see, so blesta took care of the conversion.

 

But is there any reason not to change the table column type into TIMESTAMP instead of DATETIME (for those transactional type date), and then also open the PDO connection with SET time_zone to UTC query;

this way, the functionality of the date record stays the same, but it'll make easier for database admin to view the data without requiring the date conversion.

 

Perhaps one of the guys can chime in with a more technical response, but in general we don't recommend making any structural changes to the database schema. It can have undesired effects.

[Tyson]

A TIMESTAMP data type has limited range. Instead of a 9,000 year range of DATETIME (1000 through 9999), TIMESTAMP's have a range of 1970 through 2038, which is the signed 32-bit UNIX number of seconds since that start date. This can be problematic in the near future (if it's not already) for some instances, such as service renewal dates, which are stored in the future.

 

Performing date conversions, or any other automated data manipulation, at the database level separates logic from the application. This can be useful in some cases, but has the trade-off of additional complexity in both implementation, testing, and understanding of the application. Additional complexity often leads to more bugs.

 

If auto-conversion occurred for dates retrieved by a DBA that is manually fetching records, there is the question of what timezone he should be converting to. Will the DBA know that it may not be the database's timezone, server timezone, or even the PHP timezone that needs to be used? Will he know that the timezone is dependent upon the company that the data is associated with? The company timezone is stored as a setting, and would have to be referenced for the particular data set being fetched. If not careful, it's possible to fetch records for multiple companies of different timezones in the same query, in which case some dates may be incorrect (if the dates were auto-converted).