CODEWITHSUNDEEP
phpmysqllaravellumen
Alex Haslam
Alex Haslam

Reputation: 3499

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

Upvotes: 2

Views: 65

Answers (1)

zerkms
zerkms

Reputation: 254924

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:

Upvotes: 2

Related Questions