0

I have one Mysql DB with tables which I have to manage the timezone inside the tables.

global.time_zone and session.time_zone are UTC.

I will give a short example of one table that I would like to set some conditions.

I have 5 different "IP":

for example:

  1. '1.1.1.1.1'

  2. '1.1.1.1.2'

  3. '1.1.1.1.3'

  4. '1.1.1.1.4'

  5. '1.1.1.1.5'

I have a column "updated_at" (DateTime)

now, I want to have the conditions below:

  1. IP 1 = GMT
  2. IP 2 = GMT+1
  3. IP 3 = GMT+2
  4. IP 4 = GMT+3
  5. IP 5 = GMT+4

So, according to the 'IP' on the table, I'll get the relevant timezone on "updated_at".

  1. Should I manipulate the time only for front, or there is a way to convert the TZ on the table before I'm sending a query? What is the best solution here?

  2. How to deal in the future with DST on/off?

The front is React.js, Backend is Node.js.

Thanks!

Satyr_99
  • 1
  • 2

1 Answers1

0

Storing time & date data relies on what you need to do with it in terms of retrieval, manipulation, and display.

Does it represent the past (i.e log) or the future (i.e appointment)?

The past is easy it happened and you likely know when and where. But the future, the DST could affect it.

In your case, it looks like a display problem, then you can choose who will pay the CPU bill, your company or the user.

So you can use CONVERT_TZ() to retrieve it and save in UTC'0. I would recommend you to create a VIEW with this rule. (perhaps new teammates dunno about this conversion). In this case, the processing cost is yours. On the other hand, you can transfer it to the user, and adjust the UTC'0 -> IP on the client-side.

There are some situations that become messy, for example, when you have a log that displays data from different timezones to the user. Or when you want to send an email to this user. In this case, you will have to store the user timezone aswell.

I would recommend this video.

stackchain
  • 81
  • 5