79

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

mjsarfatti
  • 1,615
  • 1
  • 15
  • 21
daGrevis
  • 20,428
  • 35
  • 96
  • 136

6 Answers6

142

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
  • 41,605
  • 12
  • 77
  • 102
  • 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
57

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
  • 367,681
  • 72
  • 526
  • 573
11

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,297
  • 4
  • 26
  • 37
7
$time_PHP = strtotime( $datetime_SQL );
psycho brm
  • 7,214
  • 1
  • 41
  • 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
  • 7
    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
  • 12,849
  • 8
  • 110
  • 182
1

Slightly abbreviated could be...

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