CODEWITHSUNDEEP
phpmysqltimestamp
Jordan
Jordan

Reputation: 71

Convert mm/dd/yyyy into mysql timestamp?

I've been trying for about two hours to get this working with no luck. I'm trying to convert a date that's entered like 11/18/2012 into a mysql timestamp but everything I've tried just ends up as either 0000-00-00 00:00:00 or NULL in the database :( I'm using PHP + MYSQL so a solution in either would be great

Upvotes: 2

Views: 6631

Answers (8)

Fathah Rehman P
Fathah Rehman P

Reputation: 8761

can you enter that example date as 2012/11/18 ?

if yes use 

select convert('2012/11/18' ,DATETIME)

or you can use

 select convert(str_to_date('11/18/2012','%m/%d/%Y'),DATETIME)

Upvotes: 0

user1726343
user1726343

Reputation:

Use STR_TO_DATE:

SELECT TIMESTAMP(STR_TO_DATE('11/18/2012','%m/%d/%Y'))

http://sqlfiddle.com/#!2/d41d8/4363/0

You stated that you tried:

UNIX_TIMESTAMP(STR_TO_DATE('$release_date','%m/%d/%y'))

The reason this doesn't work is because UNIX_TIMESTAMP's return type is unsigned integer, not TIMESTAMP. This is why you can't insert it into a TIMESTAMP column

Upvotes: 1

Mark Reed
Mark Reed

Reputation: 95325

The correct answer will depend upon exactly what you're trying to do, but in most cases it is a combination of these things:

  1. Use a DateTime object, rather than a string, to represent the timestamp in PHP. (Convert it on input rather than when writing to the database).
  2. Use a database interface that has placeholders, and when filling in a value that's a DateTime, automatically converts them to the appropriate string format

This keeps you from having to convert to, or even know, the native format expected by MySQL.

Upvotes: 0

Elby
Elby

Reputation: 1674

Try This

 $release_date=$_POST['release_date'];
    echo date("Y-m-d H:i:s",strtotime($release_date));

Upvotes: 7

Hauke Haien
Hauke Haien

Reputation: 44

I would try something like this.

$sDate = '11/18/2012';

$aDate = explode('/', $sDate);

$sMySQLTimestamp = sprintf(
   '%s-%s-%s 00:00:00',
    $aDate[2],
    $aDate[0],
    $aDate[1]
);

var_dump($sMySQLTimestamp);
> string(19) "2012-11-18 00:00:00"

Upvotes: 0

davidethell
davidethell

Reputation: 12018

My favorite method:

$date = '10/1/2012';
$mysqlDate = date('Y-m-d', strtotime($date));

Upvotes: 0

Wesley van Opdorp
Wesley van Opdorp

Reputation: 14951

PHP's DateTime to the rescue!

$datetime = new DateTime($alternativeFormat);
echo $datetime->format('Y-m-d H:i:s'); // mysql format

It's also possible to leave the altering of the data by MySQL, but I advice against it. By using the DateTime object you leave your query open to support other formats aswell.

Upvotes: 1

diggersworld
diggersworld

Reputation: 13080

Have you tried strtotime(): http://php.net/manual/en/function.strtotime.php

Or exploding on '/' and then using mktime(): http://php.net/manual/en/function.mktime.php

$parts = explode('/', $date);
$timestamp = mktime(0, 0, 0, $parts[0], $parts[1], $parts[2]);

Or any of the other suggestions in answers posted here?

Upvotes: 0

Related Questions