How can I get timestamp value in MariaDB/MySQL?

Hosting Support
7

The issue is very much timezone related. Our servers are working correctly, but the UNIX_TIMESTAMP function works differently than you think it does.

You need to realize that our servers are configured to be in the EST (UTC-5) timezone (historic reasons).

The UTC_TIMESTAMP() function returns a date and time. But as you can see, it does NOT include a timezone identifier. But Unix timestamps are always in UTC, but other dates and times may not be. So the UNIX_TIMESTAMP() function converts the time from the configured time zone (EST) to UTC, and uses that for the timestamp.

So UNIX_TIMESTAMP assumes the input value is in EST, but you’re giving it a UTC time, so of course the result will be wrong.

  • If you want to get the current date and time, you can use the NOW() function. But be aware that this time will always be in EST.
  • If you want to get the current timestamp, you can either run UNIX_TIMESTAMP(NOW()), or just UNIX_TIMESTAMP().
  • If you already have dates and times in UTC you need to work with, you will need to find another way to convert them into timestamps. I don’t think MySQL can do this for you, but PHP can.
7 Likes