PHP has time(), MySQL has UNIX_TIMESTAMP().
Will these always be equal, on a given machine?
I expect the answer is normally yes, but are there any edge cases?
2 Answers
They will always be equal on the same machine at the same time.
Do note that it's fairly common for MySQL to be hosted on a second machine, which'll open up the possibility for minor differences (in normal operation, from things like networking latency as the query's returned to the machine making the query) and possibly larger differences (if one/both of the servers have an incorrect time locally).
8 Comments
jeffjenx
Even if MySQL was on a different machine with a different timezone, shouldn't UNIX timestamp provide the same value from UTC 00:00:00? I would think the only possible difference in time would be when the execution of the process took place.
ceejayoz
@Quantastical Yes, it should provide the same value. Every timezone in the world has the exact same UNIX timestamp at any particular moment. That said, the "minor differences" I speak of are things like 1-2 seconds if your connection to the MySQL server is a bit slow or something like that.
Saic Siquot
@Quantastical, "servers can have an incorrect time locally" especially if they do not correct the time via ntp. In that case two diferent servers return two diferent timestamps at the same moment.
ceejayoz
@Quantastical Exactly. Calling
UNIX_TIMESTAMP() and time() consults the server's system clock. If that system clock is incorrect, the resulting timestamps will be similarly incorrect. NTP is a service that will continually consult time servers around the world to keep the system clock accurate.jeffjenx
@ceejayoz Perfect, thank you. That is the piece I wasn't sure about, whether or not that function call pinged some outside resource or calculated it based off the system clock. I approve of your answer :) Thank you again for spending time helping me.
|
Yes, they will always represent the same time, plus or minus any differences in the time it takes to execute one another.
This is because both time() and UNIX_TIMESTAMP() return the number of seconds since the Unix Epoch, and the Unix Epoch is UTC, which is by-design agnostic to time zones and daylight savings time.
It should also be noted that if you're working with a specific date that you're converting into a unix timestamp, then time zones WILL make a difference.
1 Comment
ceejayoz
@Quantastical You're misreading my answer. I'm noting on separate machines you could see differences due to time slippage, not using NTP, etc. Timezones have nothing to do with it.
UNIX_TIMESTAMP(field)whenfieldis aDATETIMEcan get hairy. We store as date/timeTIMESTAMPinstead ofDATETIMEand use carbon.nesbot.com/docs for the PHP manipulation.