Alex Haslam Alex Haslam - 2 months ago 10
MySQL Question

Call to DB returning incomplete data

I have an SQL Query:

select count(*) AS revolutions, DATE_FORMAT(time, '%Y-%m-%d %H') as time_period from `raw_data` where `time` >= '2016-09-10 21:51:33' group by `time_period`


This returns the following data in a MySQL client:

revolutions | time_period
630 | 2016-09-10 23
2062 | 2016-09-11 00
1839 | 2016-09-11 01
377 | 2016-09-11 02
83 | 2016-09-11 03
325 | 2016-09-11 04


In Laravel, I build an identical query that a dump of looks like this:

["sql"]=>
string(136) "select count(*) AS revolutions, DATE_FORMAT(time, '%Y-%m-%d %H') as time_period from `raw_data` where `time` >= ? group by `time_period`"
["bindings"]=>
array(1) {
[0]=>
string(19) "2016-09-10 22:02:02"
}


but this returns the following set of data:

[
{

"revolutions": 1863,
"time_period": "2016-09-10 22"
},
{
"revolutions": 1839,
"time_period": "2016-09-10 23"
},
{
"revolutions": 377,
"time_period": "2016-09-11 00"
},
{
"revolutions": 83,
"time_period": "2016-09-11 01"
},
{
"revolutions": 325,
"time_period": "2016-09-11 02"
}
]


What could be causing the missing data on 02, and the non-existent data on 03 and 04?

Edit: Time zone was not set in Lumen.

Fix was to add a config value to the .env

DB_CONNECTION=mysql
DB_TIMEZONE=+02:00

Answer

It appears that time is of timestamp type, which converts the time into a timezone that the client should specify explicitly right after they connected.

To set it explicitly one must emit the

SET time_zone = timezone;

query.

And the default value comes from the config (or CLI parameters) directive default-time-zone

References: