There are a lot of questions that ask about 'UNIX timestamp to MySQL time'. I needed the reversed way, yea... Any idea?
Add a comment
|
6 Answers
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
1 Comment
Eric Seastrand
Be careful with time zones when using strtotime. See this question: stackoverflow.com/questions/10417179/…
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);
3 Comments
Fabien Henon
That won't work if the field is a TIME field, only DATE, DATETIME or a string with days, months and years
Irvan Hilmi
$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
$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:19From 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"]))
{...}
Comments
$time_PHP = strtotime( $datetime_SQL );
2 Comments
ChrisF
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)
psycho brm
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?
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.
