0

I am using NodeJS/javascript to determine the current date which has the time zone of the system. Now i want to insert this date into MySQL with UTC+0. After that I want to select this date with NodeJS/javascript from MySQL again with UTC+0 and insert it now into MS SQL where I want it to be stored again as UTC+0. At the end I select with NodeJS/javascript again, but this time from the MS SQL and also I want the date this time to be in the timezone which is used right now in the system wherever I am in the future.

Here's an example: For 01.01.2018 15:00:00 UTC+1(German winter time) I want it to be stored in both databases with UTC+0. When summer comes and the time zone of my system changes to UTC+2(German summer time) or I move to another country with e.g. UTC-5 I want to get the date from MS SQL to be converted into the time zone my system uses automaticly.

So the questions are:

  • How can I insert my date from NodeJS with the systems time zone to MySQL with UTC+0?
  • How can I select this date, so that I can insert it with NodeJS into the MS SL and it's UTC+0 there too?
  • How can I select the date from MS SQL with NodeJS so that it has the systems time zone again?

Additional info: I have two programs in NodeJS communicating with each other. the first one (client) is inserting a date into his MySQL database and sends this to date also to the server application. the reason why he's inserting it into his own MySQL database is for synchronisation reasons when the server is not running, to get them another time. When the server receives the dates from the clients, those should be stored in the servers MS SQL database. admins of the server are able to export those dates into Excel whenever they want. but the dates have to be converted from the date in the MS SQL (UTC+0) into the servers system time zone, so that in the excel file the date is in the systems time zone instead of UTC+0

T. Yueksel
  • 69
  • 1
  • 10
  • You mean when the day-light time savings come you want automatic time zone conversion? A bit more clarification would help, how much user involvement is there, will the user trigger this query, or are you looking for a server daemon job? – Mavi Domates Dec 18 '18 at 13:41
  • The MySQL database is on client side and i want to send those dates with NodeJS to a server with the MS SQL database. The dates in the MS SQL database will be exported later into a Excel file on server side with the server systems time zone – T. Yueksel Dec 18 '18 at 13:53
  • What do you mean your MySQL is on the client side? Can you describe the problem from user's perspective, this looks like an XY problem presently. – Mavi Domates Dec 18 '18 at 13:56
  • i updated the question above – T. Yueksel Dec 18 '18 at 14:08

2 Answers2

1

I think there are some optimizations which can be made across this solution, but I'll answer your questions first.

How can I insert my date from NodeJS with the systems time zone to MySQL with UTC+0?

You get the system time zone using new Date() - to convert that to UTC format which MySQL would understand you can do new Date().toISOString()

To store this value in MySQL you need a DateTime field. Just inserting that value should work, but if it doesn't just parse it for it to be a date.

How can I select this date, so that I can insert it with NodeJS into the MS SQL and it's UTC+0 there too?

Well you've inserted it in a standard format which is always UTC+0. You just select it and insert it the same.

How can I select the date from MS SQL with NodeJS so that it has the systems time zone again?

So - you have a DateTime field in MS SQL which you'll be querying through Node.JS - the field is in UTC+0 and you want to get that time accordingly for your server's timezone?

Well - if you do new Date().getTimezoneOffset() that should give you your timezone offset. So it should become something like this:

var date = MSSQLWrapper.someOperation('select d_field from ...');
var offset = new Date().getTimezoneOffset(); // Your offset
date.setHours(date.getHours() + offset); // Add your offset
console.log(date) // your aligned date.

Edit

If you want to change the timezone on selection from SQL you can use AT TIME ZONE

SELECT your_date from your_table   
AT TIME ZONE 'Eastern Standard Time' AS my_new_date

You can pass the timezone name in your query from your server side.

or alternatively you can check out TODATETIMEOFFSET

This question also has some good examples: Convert datetime value from one timezone to UTC timezone using sql query

Mavi Domates
  • 4,262
  • 2
  • 26
  • 45
  • Is there also a way to handle the conversions in a sql query, so that i already select the right timezone? Another question: when i insert into MySQL, is MySQL converting the date automaticly into UTC, and if so, what time zone has it, when i select the date? – T. Yueksel Dec 18 '18 at 15:13
  • Thx but you have explicitly typed in eastern standard time, but it might be eastern summer time or even another time zone. That’s why I want it to be the same as the systems time zone – T. Yueksel Dec 18 '18 at 15:24
  • @T.Yueksel I understand - an alternative would be to pass that name down in the query from your server when querying (this is if you want to do the conversion in your SQL and not your code) Otherwise, I don't think you have another alternative but to do the conversion in the code (which is OK) – Mavi Domates Dec 18 '18 at 15:26
  • Sure! @T.Yueksel I'd appreciate the green tick if this solves your problem ;) – Mavi Domates Dec 18 '18 at 15:28
0

Use moment js and its UTC feature. This way you will not need to handle the timezones.

Greetings

Dominik

DominikHelps
  • 971
  • 4
  • 10