How to Convert MySQL Time to Unix Timestamp Using PHP

How to convert MySQL time to UNIX timestamp using PHP?

Use strtotime(..):

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

Also check this out (to do it in MySQL way.)

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Convert MySQL DATETIME to PHP UNIXTIME

You could do:

list($date, $time) = explode(' ', $yourMySQLDateTime);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);

$timestamp = mktime($hour, $minute, $second, $month, $day, $year); 

CakePHP Convert MySQL DateTime to Unix Timestamp

You are selecting a unix timestamp for an existing column, which is of some sort of date/time type, but CakePHP's date/time type converter doesn't support unix timestamps, it expects date/time-ish values in formats like Y-m-d H:i:s.

You can workaround this by simply using a different alias in the select list, eg something like:

'sample_timestamp' => $unixTimestamp,

or by remapping the type for the query:

$query->getSelectTypeMap()->addDefaults(['sample_time' => 'integer']);

I'd suggest that you also look into converting the value on PHP level when it's actually needed, the date/time objects that the ORM creates for your date/time-ish column has a getTimestamp() method, so you could do:

$entity->sample_time->getTimestamp()

PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

Your date format is wrong... i is for minute, not m (months).

return date("Y-m-d H:i:s", $unixTimestamp);

A few side notes:

• There's no need to re-assign, i.e. $unixTimestamp = $unixTimestamp;
• Since you're using PHP > 5.3. you may be interested in the new DateTime object.

How to convert MySQL TIMESTAMP to date time in PHP

To do this the OO (and most flexible) way use DateTime class and use the static createFromFormat method to instantiate a new DateTime object:

$new_datetime = DateTime::createFromFormat ( "Y-m-d H:i:s", $row["timestamp"] );

Now you can use the $new_datetime object to generate any string representation you'd like by calling the object's format method:

echo $new_datetime->format('d/m/y, H:i:s');

To boot, you since you've a DateTime object you can now also to any manner of transformation (like shifting timezones or adding days), comparison (greater or less than another DateTime), and various time calculations (how many days/months/etc... between this and another DateTime).

DateTime:http://php.net/manual/en/class.datetime.php
Learn it. Love it. Live it.

Converting date/time string to unix timestamp in MySQL

The UNIX_TIMESTAMP() function requires a valid date/time format to convert correctly, so you need to convert your existing date/time format to a valid/recognised format (including the year) first. You can do this using MySQL's STR_TO_DATE() function, telling it what format you are passing in, and concatenating in a hard-coded year value as it's always 2016 in your case.

STR_TO_DATE(CONCAT('2016-', <your date/time value>), '%Y-%d %b %h:%i%p')

You can then use the UNIX_TIMESTAMP() function to convert that valid date to your unix timestamp and update all those records in a single step:

UPDATE table_name
   SET new_timestamp = 
       UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p'));

Converting TIMESTAMP to unix time in PHP?

You're looking for strtotime()

---

Related Topics