How to place the current date and time in a datetime field?

Asked

Viewed 5,886 times

2

I have a field date in a table that is of the type datetime and I want to put there the current date of my server.

inserir a descrição da imagem aqui

I’ve tried everything with NOW() in the insert SQL, but then I saw that it doesn’t work. I tried to change the DEFAULT country date for CURRENT_TIMESTAMP and nothing.

In PHP I already tried with the function date, and gmdate,

date("Y-m-d H:i:s");
gmdate("Y-m-d H:i:s");

and I still have 0000-00-00 00:00:00 in the database, this when I try to insert a record in the table.

php mysql datetime

  • and if before you set date_default_timezone_set('America/Sao_paulo'); what happens?

  • Have you ever tried to define your field like this? date datetime NOT NULL DEFAULT NOW(),

  • What version of your Mysql ? Try using Current_Date.

  • 1

    I found this post: http://optimize-this.blogspot.com.br/2012/04/datetime-default-now-finally-available.html. What we usually do is leave the type field timestamp and then use the current_timestamp or now() as default.

  • @Marconciliosouza ERROR 1067: Invalid default value for 'date'

  • 1

    @Zoom is MySQL 5.1.73. But I wanted it to be datetime.

  • @Jorgeb. shows the structure of your table

  • @psantos is done.

  • @Jorgeb. first hint: change the field name date for data or my_date. date is a type for Mysql soon there will be confusion

  • 2

    @you’re right it was just an experiment. And I figured out the problem, I’m using mysqli_ and was using 'i' as parameter type and has to be’s'.

Show 5 more comments

1 answer

2

Well, I don’t know if that’s exactly what I understood. Suppose you have the following table:

CREATE TABLE `test`.`tasks` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`)
);

To insert a log with the current server date, the following command is sufficient:

INSERT INTO tasks (created_at) VALUES (now());

mysql> select * from tasks;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2016-01-19 11:49:58 |
+----+---------------------+
1 Row in set (0.00 sec)

Using PHP (with PDO), just do the following:

<?php

// algo mais ou menos como: 2016-01-19 11:56:30
$data_actual = date('Y-m-d H:i:s');


$sql = "INSET INTO tasks(created_at) VALUES (:created_at)";
$stmt = $pdo->prepare($sql);

$stmt->bindParam(':created_at', $data_actual);
$stmt->execute();

UPDATE:

Note: By error, you are trying to add a type value datetime in a field of the kind date

UPDATE 2:

By table structure, the field in name date is or may cause problems. Change the name of this field. Yes date is used as a type of Mysql data (http://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html).

After you do this update, you can do the Insert this way:

INSERT INTO tasks (created_at) VALUES (now());

Browser other questions tagged php mysql datetime

You are not signed in. Login or sign up in order to post.