I am looking to provide simple logs to my users via my api from different time zones and I am a bit lost would appreciate some help.
I have the following table called logs:
CREATE TABLE `logs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`ip` VARCHAR(45) NOT NULL,
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And I have an hourly table to bind my query over 24 hours.
CREATE TABLE `log_hours` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`hourId` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO log_hours
(hourId)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23),
(24);
Every time I get an api hit I store the log take the following data.
id | userId | ip | timestamp
1 1 0.0.0.0 2018-08-23 14:20:34
2 1 0.0.0.0 2018-08-23 14:20:34
3 1 0.0.0.0 2018-08-23 14:20:34
4 1 0.0.0.0 2018-08-23 14:20:34
5 1 0.0.0.0 2018-08-23 14:20:34
Now if I run my query like below.
SELECT DW.hourId AS hour, ifnull(t.hits,0) hits from log_hours DW left join (
SELECT COUNT( * ) AS hits, HOUR( logs.timestamp ) AS `hour`
FROM logs WHERE DAY( logs.timestamp ) = DAY(CURDATE())
AND MONTH( logs.timestamp ) = MONTH(CURDATE())
AND `userId` = 1 GROUP BY HOUR( logs.timestamp )) t on DW.hourId = t.hour
ORDER BY hour ASC
This works fine and gives me the logs for each hour like this.
Now I am stuck on time zones my database time zone is set to BST.
Say I have a user in America for instance if they hit the api in their time zone does mysql automatically convert their time zone to my BST time zone so the output data would make sense?
Should I be using datetime NOT NULL DEFAULT CURRENT_TIMESTAMP or should I be using an INT and using php time() function to insert the record?
Should I return the data first via php from my timezone and then convert to theirs what is best practice in this scenario?
EDIT:
First of sorry if I am asking stupid questions here but really want to fully understand the process.
At what point do I retrieve the user's local timezone? should I add it as a required param to the API.
Here is my PHP code I am just adding everything to one function makes it easier to read my process is MVC.
public function usage_get(){
$userId = $this->get('userId'); // i am obviously not getting the users info this way this is just for the example
// DO I MAKE THEM SEND THEIR TIMEZONE TO THE API AS A PARAM THEN USE IT IN MYSQL
//https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz?
$timezone = $this->get('timezone');
// DO I SOMEHOW RETRIEVE THE TIME ZONE BASE ON IP AND GET IT THAT WAY?
$query = $this->db->query("SELECT DW.hourId AS hour, ifnull(t.hits,0) hits
from log_hours DW
left join (
SELECT COUNT( * ) AS hits, HOUR( logs.timestamp ) AS `hour`
FROM logs
WHERE DAY( logs.timestamp ) = DAY(CURDATE())
AND MONTH( logs.timestamp ) = MONTH(CURDATE())
AND `userId` = ".$this->db->escape_str($userId)."
GROUP BY HOUR( logs.timestamp )
) t on DW.hourId = t.hour ORDER BY hour ASC");
// DO I RUN A PHP FUNCTION AFTER THE MYSQL BASED ON TIMEZONE?
if($query->num_rows() > 0){
$this->response([
'status' => TRUE,
'message' => 'Success',
'data' => $query->result(),
], REST_Controller::HTTP_OK);
}else{
$this->response([
'status' => FALSE,
'message' => 'Error'
], REST_Controller::HTTP_OK);
}
}
