1

As per the MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

I am ok with the conversion for the retrieval but for storage I already have an UTC timestamp. It is sent like this from the client as it is a value calculated on the client side and I want to avoid time zone conversions (because I think this will add complexity to the client-server communication).

How can I insert this value avoiding further conversion?

Super Rey
  • 375
  • 2
  • 11

2 Answers2

0

If you don't want MySQL to do any conversions, maybe the simplest way is to just store the timestamp in a numeric field.
This might be bad practice depending on what you are going to do with it, this thread has some discussion on the pros and cons: Using MySQL's TIMESTAMP vs storing timestamps directly

Community
  • 1
  • 1
glaux
  • 701
  • 7
  • 20
  • Thanks @glaux, I still want the conversion on retrieval. As the timestamp is computed in the client side I thought that it will be simpler to just send the timestamp in UTC to avoid dealing with client timezone vs server timezone issues. But again I am still interested in the conversion at retrieval, so I will go on with the TIMESTAMP field type. – Super Rey Apr 03 '17 at 09:15
0

For now I am using this workaround:

CONVERT_TZ('2009-06-15T13:45:30.0000000Z','+00:00','SYSTEM')

I would prefer no to convert a value that will be converted again (although internally) but it seems to be the more convenient solution for me.

Another option is to use:

SET time_zone = '+00:00';
--- your query here
SET time_zone = 'SYSTEM';

But in my case as I only needed to convert only two fields and that seemed like overkill although in this case there should be no conversion at all.

Super Rey
  • 375
  • 2
  • 11