Suggestions for MySQL on saving time information

I saw some netizens on the Internet saying that during the process of checking statistical data with SPs in India recently, they found that there was a time zone problem in the statistical data. This article mainly addresses the above issues. The interface he provides to the other party to obtain the statistical results of a specified date is controlled according to China's time zone. Fortunately, the international timestamp is stored in the database, so you only need to determine the time zone of the caller at the interface to obtain the correct statistical result information.

The solution reference is as follows, that is, based on the SimpleDateFormat object, it is necessary to clarify the current time zone that needs to be used.

SimpleDateFormat.setTimeZone(TimeZone);

// Java:
long timeMs = System.currentTimeMillis();
System.out.println("long = " + timeMs);

// current time zone
SimpleDateFormat default = new SimpleDateFormat("yyyy-MM-dd HH:mm");
System.out.println(default.format(timeMs));

// +8:00 time zone
SimpleDateFormat chinaFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
chinaFormat.setTimeZone(TimeZone.getTimeZone("GMT+8:00"));
System.out.println("GMT+8:00 = " + chinaFormat.format(timeMs));

// +5:30 time zone
SimpleDateFormat indiaFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
indiaFormat.setTimeZone(TimeZone.getTimeZone("GMT+5:30"));
System.out.println("GMT+5:30 = " + indiaFormat.format(timeMs));

Using the above method can actually solve the time zone problem, because I obtained the statistical data through the hour table and the hour The table has one record per hour. The Indian time zone is GMT 5:30. Based on the current table structure, there will be a half-hour data deviation problem. Therefore, in order to solve this problem, we modify the statistical granularity of the hour table to half an hour.

Summary:

This blog is just a simple solution to the key points of the time zone solution. The premise of using this solution is that the database stores international timestamps! ! !

Only time storage based on absolute timestamp, there is no time zone problem at all! ! ! Time zone is just a display issue! ! !

Make a little progress every day~

[Recommended course: MySQL video tutorial]

The above is the detailed content of Suggestions for MySQL on saving time information. For more information, please follow other related articles on the PHP Chinese website!