Best way to get unix timestamp for current UTC time in MySQL

here’s a different slant to appear at: if you possess two MySQL hosting servers readied to various opportunity zones (along with genuine variation in offset, as well as supposing both hosting servers are actually time-synchronized along with NTP), both of all of them will provide you the very same market value for UNIX_TIMESTAMP() when the query is implemented in the exact same 2nd time-frame. I would certainly think that if Unix timestamp possessed some concept of timezone, it would come back different values for different timezones. Yet that isn’t the case.

I have actually been attempting to use the observing in mySQL to create a unix seal for present UTC time. Is actually there a far better means or even a singular order merely to obtain a UTC unix timestamp in mySQL?

I was thus used to the UNIX timestamp and currently switched over to the DateTime item. I saved DateTime things in MySQL. It took an even though just before I recognized that the timezone of the DateTime things performs not acquire kept in the db. I need to monitor that on the edge. When I read the DateTime data back in to another page, this page just thought that it was actually UTC!!! So I must begin the page along with date_default_timezone_set(‘America/Denver’); (I make use of GoDaddy web server.) I suched as the idea of UNIX timestamp even more. They are private of timezone.

As I comprehend, UNIX_TIMESTAMP() will definitely always coincide, regardless of the timezone of the hosting server. That is what I made use of just before. In the instance over it is actually used with a debate UNIX_TIMESTAMP(time) in THIS situation the web server timezone are going to be used to analyze the specification time.

I may convert a unix timestamp to a Time() item through placing the lengthy market value into the Day() constructor. For eg: I could possibly have it as brand new Time(1318762128031).

you are actually right, with a web server that’s certainly not established to UTC, what you stated is actually reproducible. I wish to highlight that this is still an insect on the component of MySQL and also carries out NOT signify that a Unix timestamp possesses a time region. What is actually occurring is that the output of UTC_TIMESTAMP() does not have a timestamp fastened, therefore when MySQL gets it as an input for UNIX_TIMESTAMP(input), it translates the input in the MySQL’s timezone setting, as a result providing the Unix timestamp for that incorrect interpretation.

Really the Unix Timestamp possesses no inherent timezone. It is actually a time symbols that is actually primarily made to be not tied through ideas like timezones, daylight cost savings as well as clock branches. It’s extremely simply an amount: the number of few seconds (or even nanoseconds) because the minute in time that could be pertained to as ‘1970-01-01 00:00:00’ UTC (which is Unix time 0). Yet Unix time itself is actually not UTC, it transcends the principle of timezone on its own. No concern which timezone you remain in, the present Unix time is actually precisely the exact same for you, a person on a different timezone, and also the whole universe.

After that, how can I get back the unix timestamp from the Date() things?

in fact getTime() doesn’t return the Unix timestamp however “Unix timestamp * thousand”. The agreed defintion about the Unix timestamp is that it gives opportunities considering that the date in second, not nanoseconds; ).

getTime() recovers the nanoseconds due to the fact that Jan 1, 1970 passed to the manufacturer. It must not be actually too hard to receive the Unix opportunity (same, yet in few seconds) from that.

It comes back the unix-timestamp in milliseconds. Yes, the unix-timestamp is accepted to reside in seconds – however if your solution would certainly hold true, (getTime() = unixTimestamp * 1000), getTime() would regularly return three Nos in the end, however as a matter of fact may return just about anything coming from ending with 000 to 999, which indicates it has a greater accuracy because of nanoseconds as well as is actually certainly not simply “* 1000”. Significance: Matching up a true unixTimestamp * thousand with the getTime() lead would simply succeed in ~ 0.1% of the scenarios.

Leave a Reply

Your email address will not be published. Required fields are marked *