CODEWITHSUNDEEP
phpmysql
Haris
Haris

Reputation: 14053

Mysql format date to insert to table

I need to format and insert the date to mysql table which is type of datetime, I am doing this from php.

Here is the statement

$timestamp ="22-11-2015+00:00:00";
$mysqltime = date ("d-m-Y H:i:s", $timestamp);

But when I insert to table the date is wrong, it showing 31-12-1969 19:00:22

What could be the issue?

Upvotes: 1

Views: 336

Answers (4)

Bledi
Bledi

Reputation: 11

I think the corret format would be:

$mysqltime = date_format ($timestamp,"d-m-Y H:i:s");

Let me know if this works

Upvotes: 1

Hasse Björk
Hasse Björk

Reputation: 1601

This should be working, but did not work for me!

$timestamp ="22-11-2015+00:00:00";
$mysqltime = date ("d-m-Y H:i:s", strtotime($timestamp));

I believe it is because my national standard format (LC_TIME) is "Y-m-d" rather then "d-m-Y". However this works perfectly:

$timestamp ="22-11-2015+00:00:00";
// The '+' sign needs to be escaped!!
$time = date_create_from_format( "d-m-Y\+H:i:s", $timestamp);
$mysqltime = $time->format( 'd-m-Y H:i:s' );

On the other hand, MySQL use the date format "YYYY-MM-DD" as a standard. The correct format string should be:

$mysqltime = date ("Y-m-d H:i:s", strtotime($timestamp));
// or second option
$mysqltime = $time->format( 'Y-m-d H:i:s' );

Upvotes: 1

Thinkadoodle
Thinkadoodle

Reputation: 552

The date function expects an integer timestamp for the second argument.

This MSQL insert statement would work fine:

INSERT INTO mytable (birthday) ('2015-11-22 00:00:00')

If you're using a database class in PHP and the function you're calling is using a PHP date object, then you'd have to construct the PHP date object using the date() function.

Or if you want to use PHP code to reformat the date in PHP before sending the string to the database to be inserted, then I think you'd use strtodate to get a PHP date object, and then date()

$phpdate = strtodate('d-m-Y H:i:s', '22-11-2015 00:00:00')
$dateformysql = date('Y-m-d H:i:s', $phpdate)
databaseclass.dotheinsert($mysqldate)

Upvotes: 1

Alon Eitan
Alon Eitan

Reputation: 12025

It should be

$timestamp ="22-11-2015+00:00:00";
$mysqltime = date ("d-m-Y H:i:s", strtotime($timestamp));

because date is expecting it's second parameter to be a number, specifically the number of seconds since midnight January 1st, 1970.

Upvotes: 3

Related Questions