How to insert a midnight datetime into mysql?

Question

I’m using the Laravel Framework 5.8, I am trying to register a record in the database where I need to set in API some dates.

Although they are CURRENT_TIMESTAMP, i also need to be able to enter a record defined by parameter through this API (in this table, I have 4 date fields: start, end, updated, created):

Here begins the problem:

The problem only happens when the date is midnight, ie: 00:00:00

First I tried to insert in the same American format, and gave the following error:

"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-11-04 00:10:10' for column 'end' at Row 1 (SQL: Insert into Schools (scb_code, owner_id, name, cnpj, zipcode, neighborhood, city, state, phone, site, number, plan, start, end, access, address, guten_version, type, domain, scn_id) values (1, 2, Escola teste, 123, 123, Jardim Paulista, São Paulo, SP, (11) 123-123, http://www.xxx.com.br, 123, 14, 2018-05-04 15:46:39, 2018-11-04 00:10:10, ? , Al. Xyz, ? , 1, ?, ?))"

So I created a method for the date to convert to the format timestamp since it is a timestamp the field of the bank, I believed that it would solve the problem, but, it did not solve

Fail method:

public static function setFormatDateTimeToTimestamp($data, 
                                                    $fields = ['start', 
                                                               'end',
                                                               'created', 
                                                               'updated']
)
{
    if (count($fields)) 
    {
        foreach($fields as $field) 
        {
            if (isset($data[$field]) && 
                $data[$field] != null && 
                strrpos($data[$field], " 00:") !== false) 
            {

                $dt = \DateTime::createFromFormat('Y-m-d H:i:s', $data[$field]);
                if ($dt) 
                {
                   $data[$field] = $dt->getTimestamp();
                }
            }
        }
    }
    return $data;
}

Gave in the same, only different, the error still persists:

"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1541290210' for column 'end' at Row 1 (SQL: Insert into Schools (scb_code, owner_id, name, cnpj, zipcode, neighborhood, city, state, phone, site, number, plan, start, end, access, address, guten_version, type, domain, scn_id) values (1, 2, Escola teste, 123, 123, Jardim Paulista, São Paulo, SP, (11) 123-123, http://www.xxx.com.br, 123, 14, 2018-05-04 15:46:39, 123, ? , Al. Santos, ? , 1, ?, ?))"

The table is in this format:

CREATE TABLE `Schools` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `scb_code` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `scb_code_owner` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `owner_id` int(11) NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `cnpj` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `zipcode` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `neighborhood` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `site` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `number` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `plan` text COLLATE utf8_unicode_ci NOT NULL,
  `start` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `end` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `access` tinyint(2) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `guten_version` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` tinyint(4) DEFAULT '0',
  `domain` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `scn_id` int(2) DEFAULT NULL,
  `disable_block_content_teacher` tinyint(4) DEFAULT '1',
  `external_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `fk_Schools_schools_base1_idx` (`scb_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1589 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now I don’t understand why through Mysql Workbench in a newer version I can insert the radius of the date in the format I want using a simple INSERT, but when I’m using the Laravel Framework, I have this problem, which actually seems to be a problem of MySQL and not of Laravel, or is the Laravel who’s sabotaging me in MySQL, someone knows what’s going on here?

That one documentation can perhaps answer the problem, but I still don’t understand what’s wrong. And this only happens when the month is 11.

Answer 1

The problem of dates is very common. The problem usually comes from the fact that the server is not in your country. The solution but easy and put at the beginning of each page:

// Para todas as datas
date_default_timezone_set('America/Sao_Paulo');
// Para triagem e todas as funçoes de "string"
$ret = setlocale(LC_ALL, "pt_BR.utf-8");

After that, you need to think only "I’m in Brazil" and stop using a US format.

The other point comes from the kind you choose for the fields:

• "end" is timestamp type. And "2018-11-04 00:10:10" is not a Time Stamp.

• Please insert "2018-11-04 00:10:10" in a "date" field will insert "2018-11-04"

• Please insert "2018-11-04 00:10:10" in a field type "datetime" will insert "2018-11-04 00:10:10"

• Please insert "2018-11-04 00:10:10" into a "timestamp" field will give error.

Either you change the type of your field, or you turn the date into TimeStamp using mktime().

Just to put an additional info: if you try to record "2018-11-04 00:00:00" in a "date" field or in a "datetime" field the two cases will give the same result, because "2018-11-04 00:00:00 = 2018-11-04". The difference will be visible only with value type "2018-11-04 00:10:10" why "2018-11-04 00:10:10 != 2018-11-04". This may explain the confusion.

Answer 2

The problem has been solved. Mysql version 5.7.x does not have the incorrect Timezone from Brazil, correction:

SET @@global.time_zone = '+3:00';
QUIT