1

I am working in a codebase where I have an unfortunate circumstance.

A row is being inserted into the MySQL database where a time column is being set as null, but the column is nn and defaults to NOW().

However, I need that value. Without using a select query to pull that time, is there any way I can ensure that a PHP function's output is the exact same value?

Does time() === now() always? Can timezone settings screw it up? What are the implications of doing the code like this?

Josh
  • 3,258
  • 2
  • 19
  • 31

2 Answers2

2

Few things

Mysql now() and PHP time() are not same. PHP time() will be an unixtime stamp representation in digits, where as mysql now() will be in the format Y-m-d H:i:s

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-04-14 13:15:10 |
+---------------------+
1 row in set (0.00 sec)

And in php time() will return something as 1428997570

In mysql you can use the function unix_timestamp()

mysql> select unix_timestamp() ;
+------------------+
| unix_timestamp() |
+------------------+
|       1428997518 |
+------------------+

The most important part is if the apache timezone and mysql timezone does not match then you are likely to get different values using time() and unix_timestamp() and hence the comparison fails

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Yeah, this is exactly what I was fearing. Turns out MySQL has stored 10 million records in its local timezone. I did some [more reading](http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql) and it turns out to even get MySQL's timezone you also need to run a query, which defeats what I'm going for. So, to Google explorers, the answer appears to be: **Redo your code so that this isn't a problem.** – Josh Apr 14 '15 at 07:53
  • If your mysql timezone is different than that of your apache then you still have a function to do the conversion while matching https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz – Abhik Chakraborty Apr 14 '15 at 07:58
1

time() Function in PHP return the time in secounds since 1970, as to: time in php manual

The function in MySQL equal to this is: UNIX_TIMESTAMP as to: unix_timestamp from MySQL manual

Thefore time()==unix_timestamp()

Hope thats help! :)

Itay Elkouby
  • 344
  • 1
  • 15