80

There are a lot of questions that ask about 'UNIX timestamp to MySQL time'. I needed the reversed way, yea... Any idea?

mjsarfatti
  • 1,725
  • 1
  • 15
  • 22
daGrevis
  • 21,014
  • 37
  • 100
  • 139

6 Answers6

146

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

UltraInstinct
  • 43,308
  • 12
  • 81
  • 104
  • 5
    Be careful with time zones when using strtotime. See this question: http://stackoverflow.com/questions/10417179/is-the-result-of-strtotime-is-changed-based-on-the-timezone – Eric Seastrand Jul 06 '15 at 16:00
61

You can mysql's UNIX_TIMESTAMP function directly from your query, here is an example:

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');

Similarly, you can pass in the date/datetime field:

SELECT UNIX_TIMESTAMP(yourField);
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • That won't work if the field is a TIME field, only DATE, DATETIME or a string with days, months and years – Fabien Henon Mar 13 '15 at 09:08
  • `$query = "SELECT UNIX_TIMESTAMP(update_time) FROM information_schema.tables WHERE table_schema= '$table' AND table_name= '$name'";` why does the update_time is missing? – Irvan Hilmi Feb 16 '23 at 04:38
  • `$query = "SELECT update_time FROM information_schema.tables WHERE table_schema= '$table' AND table_name= '$name'";` is returning a single row with update_time returning `2007-11-30 10:30:19` – Irvan Hilmi Feb 16 '23 at 04:39
12

From one of my other posts, getting a unixtimestamp:

$unixTimestamp = time();

Converting to mysql datetime format:

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

Getting some mysql timestamp:

$mysqlTimestamp = '2013-01-10 12:13:37';

Converting it to a unixtimestamp:

$unixTimestamp = strtotime('2010-05-17 19:13:37');

...comparing it with one or a range of times, to see if the user entered a realistic time:

if($unixTimestamp > strtotime("1999-12-15") && $unixTimestamp < strtotime("2025-12-15"))
{...}

Unix timestamps are safer too. You can do the following to check if a url passed variable is valid, before checking (for example) the previous range check:

if(ctype_digit($_GET["UpdateTimestamp"]))
{...}
Florian Mertens
  • 2,418
  • 4
  • 27
  • 37
9
$time_PHP = strtotime( $datetime_SQL );
psycho brm
  • 7,494
  • 1
  • 43
  • 42
  • 2
    Some explanation of **why** this answers the question would make the answer more useful. *(I know there are probably lots of other one line answers out there, but they haven't just been added)* – ChrisF Nov 09 '12 at 16:32
  • 9
    The question was how to convert $mysql_timestamp into $php_timestamp, the answer is that "strtotime" does it, as shown. What's there to explain? – psycho brm Nov 16 '12 at 20:36
5

Instead of strtotime you should use DateTime with PHP. You can also regard the timezone this way:

$dt = DateTime::createFromFormat('Y-m-d H:i:s', $mysqltime, new DateTimeZone('Europe/Berlin'));
$unix_timestamp = $dt->getTimestamp();

$mysqltime is of type MySQL Datetime, e. g. 2018-02-26 07:53:00.

Avatar
  • 14,622
  • 9
  • 119
  • 198
2

Slightly abbreviated could be...

echo date("Y-m-d H:i:s", strtotime($mysqltime));
mintedsky
  • 1,073
  • 13
  • 18