2

I setup both PHP and mySQL conf files to be have default timezone = 'Europe/Rome' (or at least I think I did it)

if i query mysql

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

mysql> SELECT CURRENT_TIMESTAMP(); // it's 6:38pm in NYC now
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2016-02-09 18:38:45 |
+---------------------+

but i live in NY, the server is located in US (MediaTemple) and when i test my query it saves with New_York time converted in 24h format:

eg. i just entered a row in my table using ...time = NOW()... here in NYC is 6:31pm but the time in mysql row displays 2016-02-09 18:31:47 where i was expecting to see 2016-02-09 00:31:47 which is time in Rome/Berlin

what am i doing wrong?? where do i need to set up the timezone then?

Francesco
  • 24,839
  • 29
  • 105
  • 152
  • What do you get from `date` on the command line. http://askubuntu.com/questions/323131/setting-timezone-from-terminal/323163 – Dan Feb 09 '16 at 23:44
  • if you don't have super privileges to use `SET`, you'll need to use a PHP method using date's timezone http://php.net/manual/en/function.date-default-timezone-set.php. That's what I had to do on a few servers where there are none of those privileges allowed. or use `SELECT UNIX_TIMESTAMP...` from within http://stackoverflow.com/a/19069310/ – Funk Forty Niner Feb 10 '16 at 00:49

1 Answers1

0

SYSTEM means MySQL is using the operating system's time zone. In a terminal on that machine issue this command:

date +%Z

This is the time zone used in your queries too. If it's not what you expect change the time zone at the OS level. This will vary depending on what operating system you're running (which you haven't mentioned).

Asaph
  • 159,146
  • 25
  • 197
  • 199