If instead you wanted to add three days to an existing timestamp, you would use: SELECT DATE_ADD( ' 11:52:06', interval 3 day) īoth functions work the same, the first argument is the timestamp you are starting with, and the second argument is the interval to add or subtract. For example, you can subtract two weeks from the current date with the SQL statement: SELECT DATE_SUB( now(), interval 2 week) Thankfully MySQL has the excellent DATE_ADD() and DATE_SUB() functions making this task extremely easy. The three arguments passed to CONVERT_TZ() are first the datetime / timestamp you're starting with (use now() for current time), the second will always be '+0:00' since all dates are forced to UTC in the database, and the last is the offset we wish to convert the date to. This results in 13:47:23 which is exactly correct. You need to know the offset first, for example, PST on the west coast of North America is UTC -08:00, so you could use: SELECT CONVERT_TZ( ' 21:47:23', '+0:00', '-8:00')
When necessary you can easily convert the time zone of any datetime / timestamp value with the handy CONVERT_TZ() MySQL function. Since all dates will now be saved in UTC, you always know what you're working with, making things more simplistic and straight forward. Every time you establish a connection to the MySQL database, you should switch the time zone to UTC, which can be done with the following SQL statement: SET TIME_ZONE = '+0:00' To help keep things standardized, you should only ever work with dates / times in UTC time zone.
0 kommentar(er)
