0

I'm having trouble changing mysql time to China time, can you help?

SELECT NOW(); on my webhost produces: 2020-02-24 19:52:25

but I am exactly 15 hours ahead of that, it is 2020-02-25 10:52:25

SELECT @@system_time_zone; produces: MST

Apparently, the maximum time I can SET time_zone is '+14:00'

But what I've tried just sets my time column to all zeros.

I have a mysql table called 19OEattendance The last column is time it is a TIMESTAMP

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); produces: -07:00:00

I tried:

SET time_zone = 'Shanghai'; produces: #1298 - Unknown or incorrect time zone: 'Shanghai'

How to set the column time to my time, 15 hours in front of the time on my webhost?

I don't think I have access to my.cnf on the webhost. I can't find it.

This ups the attendance. Maybe I can tweak this?? I was hoping LOCAALTIME() would set the sender's time. Alas, no!

$sql = 'UPDATE 19OEattendance SET attendance = attendance + 1, 
     has_been_incremented = has_been_incremented + 1, time = LOCALTIME() 
     WHERE number = ' . $_POST['password'] . ' AND has_been_incremented != 1 ;';
Pedroski
  • 433
  • 1
  • 7
  • 16
  • 3
    Does this answer your question? [How do I set the time zone of MySQL?](https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql) – Pedro Coelho Feb 25 '20 at 03:23
  • **WARNING**: Whenever possible use **prepared statements with placeholder values** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Feb 25 '20 at 04:08
  • It's usually best to keep times in your database as UTC so that if you move the database, or someone in a different time-zone is working on it, the times don't jump all around. Translate to local time in your application when displaying them. – tadman Feb 25 '20 at 04:09

1 Answers1

1

I see "Asia/Shanghai" is +8. So, you can try with: SET time_zone = '+08:00'

Viet Dinh
  • 1,871
  • 1
  • 6
  • 18
  • Thanks, that helped, but still not quite right: My time is 2020-02-27 16:35:51 and "+08:00" gives me a time of 2020-02-27 01:35:51. I just tried "+15:00" and get an error, then I tried "+14:00" and get an error, then "+13:00" No error, but the time stays 15 hours behind my time!! – Pedroski Feb 27 '20 at 08:45