Mysql Set the time_zone correctly

Pedroski55 · February 17, 2020, 4:31am

This is a bit of my access19BE1inc.php It adds 1 to attendance on login. There is a TIMESTAMP. I want the time to be my time here in China. At the moment, I get a time 15 hours behind my time here.

 // attendance will not increase with multiple logins. Before next week, reset has_been_incremented to zero  	
  	 $sql = 'UPDATE 19BEattendance SET attendance = attendance + 1, 
  	 has_been_incremented = has_been_incremented + 1, time = LOCALTIME() 
  	 WHERE number = ' . $_POST['password'] . ' AND has_been_incremented != 1 ;';
    $pdo->exec($sql);

I tried putting these (not both at once) just after SET and just before attendance = in the above code:

time_zone = +15:00, and time_zone = ‘+15:00’,

but I just get a php failure.

What should I be putting? How to make my timestamp Beijing time?

phdr · February 17, 2020, 4:52am

The SET part of an UPDATE query has nothing to do with a SET time_zone = ‘+15:00’ query. You have to execute the SET time_zone = … query separately, usually immediately after making the database connection, so that all queries after that point use the new time zone.

Why are you not using a prepared query when supplying external/unknown data to a query and why aren’t you using the user’s id to relate data back to a user. By using the password value to relate data, any time a password gets reset it will break the relationship in the data.

Instead if just incrementing a value, you should store a separate row for each data item. The current method cannot distinguish between a number of attendance events and a programming mistake, duplicate form submissions, or nefarious activity.

Pedroski55 · February 17, 2020, 7:25am

Thanks, so I need to do an update on the table 19BEattendance?

Would that be as simple as:

UPDATE 19BEattendance SET time_zone = ‘+15:00’;

I suppose it can’t explode if I try it! I’m always worried I’ll break something!

Why not use a prepared query? They asked me that on linuxquestions too! 2 reasons:

That little query is part of an if clause. Prior to that, a function with a prepared statement has checked if name and password are really in the table. Were the password a bunch of sql, it would not be found in the database and the if clause would never start, because the function databaseContainsStudent1($name, $password) would not return True.
I did try to make it a prepared statement, but I got confused, so I left it alone, because it works.

Sorry, don’t really understand the last part.

astonecipher · February 17, 2020, 3:31pm

This should actually be a non-issue. The database should always use GMT and you do your calculations for display from the view using it.

Pedroski55 · February 17, 2020, 9:06pm

It is not sooo important. I can live with it.

I tried:

UPDATE 19BEattendance SET time = ‘+15:00’;

I get everything in the column time SET to zero: 0000-00-00 00:00:00:

When I login to a homework page, the time column now shows the timestamp 2020-02-17 13:59:52 .

My time is now 2020-02-18 04:59:52 (I get up early)

Maybe I can’t add 15 to that, because it goes over 24?