Store php datetime in mysql database

I can’t believe I can’t do this, but I want to be able to store the current date and time from php in to a mysql table.

The column in the table is type datetime.
I’ve tried this

$current_date = date("Y-m-d");
$my_date = strtotime($current_date);
INSERT INTO my_table (date_time) VALUES ('$my_date')

but my timestamp comes up as 0000-00-00 00:00:00

This must be so easy to do but I just can’t get it working!
I want to use the timestamp from php rather than using the mysql now() function

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this:

$my_date = date("Y-m-d H:i:s");
INSERT INTO my_table (date_time) VALUES ('$my_date');

In the date-format parameter of the date function, use :
‘H’ for 24hr format
‘h’ for 12hr format

Method 2

Don’t save it as the Unix Timestamp (which strtotime() outputs), but as “2012-12-02 13:00” into the DATETIME column.

Method 3

Create column type TIMESTAMP and set it to NOT NULL. Then pass in NULL during INSERT and MySQL will insert current date and time. This works for me.

Method 4

set the ‘type’ of column named ‘date_time’ as ‘DATETIME’ and run the following query:

INSERT INTO my_table (`date_time`) VALUES (CURRENT_TIMESTAMP)

Method 5

If you have the date in PHP as a timestamp, you can use the FROM_UNIXTIME function [1]

mysql> insert into table_name values (FROM_UNIXTIME(your_timestamp_here));

Hope it helped
[1]. https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

Method 6

Remove the strtotime()

$current_date = date("Y-m-d");
INSERT INTO my_table (date_time) VALUES ('$current_date')

If you want to include the hour, minutes and seconds,
$current_date = date(“Y-m-d H:i:s”);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating