TIMESTAMP fields are stored in UTC, but a SELECT returns
them adjusted to the server's time zone. How can I get the
timestamp in UTC, without conversion?

UTC_TIMESTAMP() returns the current timestamp only;
it doesn't take an argument. So that's no help.

CONVERT_TZ(timeval,'SYSTEM','+0:00')

turns out to work. A more correct form is

CONVERT_TZ(timeval, @@time_zone, '+0:00')

which should compensate for any server-side time zone.

This is clunky, though. Is there some way to just
retrieve the timestamp in UTC?

John Nagle

On Nov 28, 7:45*pm, John Nagle <na... (AT) animats (DOT) com> wrote:
If you want to store a UTC time stamp, just create a datetime field
and put the value in it!

John Nagle <nagle (AT) animats (DOT) com> wrote:
s/server/current connection/

By asking to get it in UTC? I.e. like so:

What is the problem with the above? Maybe you rather want to set
the time zone for that connection to UTC?

mysqldump does similar in order to preserve timestamp values:

-- near the start of the dump file
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
-- now there is data, including timestamps in UTC
-- near the end of the dump file
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

The time_zone variable exists both locally (for the current connection)
and globally (initializing the per-connection value):

http://dev.mysql.com/doc/refman/5.1/...svar_time_zone


XL

On 11/29/2010 3:00 AM, Axel Schwenke wrote:
For one hour each year in areas that use daylight
savings time, local time is ambiguous.

That's probably better.

It seems weird that there isn't some straightforward
way to retrieve TIMESTAMP values in UTC, regardless of
time zone settings.

John Nagle

Hi John,

John Nagle <nagle (AT) animats (DOT) com> wrote:
The same ambiguity is there when the timestamp is stored, because
then it is converted from local time to UTC.

If this is a concern to you, then you must either set the time zone
to UTC. Or use UTC_TIMESTAMP() and store it in a DATETIME column.

Setting the per-connection time_zone is not straightforward enough?


XL

Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:
There is this feature request:

http://bugs.mysql.com/bug.php?id=25010

and while playing with those functions I found and reported

http://bugs.mysql.com/bug.php?id=58583


XL