Coordinated Universal Time to Z time

Hi,

I do a API call to openweathermap using node-red-node-openweathermap - works 100%

The time returned is in Z time.

I then use a function none to create a insert statement to put the data into a MYSQL db.

Some how the time is changed to Coordinated Universal Time.

Now the MYSQL db doesn't like the time and inserts it as 0000-00-00 00:00:00.000000.

I have tried all sorts of different settings in the db but it stays as 0000-00-00 00:00:00.000000.

Any pointers will be appreciated.

[{"id":"71cd58d1.e9a748","type":"tab","label":"openweathermap","disabled":false,"info":""},{"id":"1ea5c8b4.fbc0c7","type":"openweathermap","z":"71cd58d1.e9a748","name":"","wtype":"current","lon":"","lat":"","city":"","country":"","language":"en","x":310,"y":160,"wires":[["31e31965.664ef6","dce1143.41e0ce8"]]},{"id":"9d0a892c.01b6d8","type":"inject","z":"71cd58d1.e9a748","name":"","props":[{"p":"location.lat","v":"-29.8064188","vt":"num"},{"p":"location.lon","v":"30.7717992","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"str","x":80,"y":160,"wires":[["1ea5c8b4.fbc0c7"]]},{"id":"31e31965.664ef6","type":"debug","z":"71cd58d1.e9a748","name":"openweathermap raw","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":500,"y":100,"wires":[]},{"id":"dce1143.41e0ce8","type":"function","z":"71cd58d1.e9a748","name":"","func":"var lat = msg.data.coord.lat;\nvar lon = msg.data.coord.lon;\n\nvar time = msg.time;\nvar city = msg.data.name;\nvar weather = msg.payload.weather; //rain,sun\nvar detail = msg.payload.detail; //light rain\nvar tempc = msg.payload.tempc; //current temp\nvar temp_maxc = msg.payload.temp_maxc; //predicted max temp\nvar temp_minc = msg.payload.temp_minc; //predicted min temp\nvar humidity = msg.payload.humidity; //predicted humidity\nvar pressure = msg.payload.pressure;\nvar windspeed = msg.payload.windspeed;\nvar winddirection = msg.payload.winddirection;\nvar sunrise = msg.payload.sunrise;\nvar sunset = msg.payload.sunset;\nvar clouds = msg.payload.clouds;\nvar visibility = msg.data.visibility;\n\nmsg.topic = \"INSERT INTO weathermap_t(lat, lon, time, city, weather, detail, tempc, temp_maxc, temp_minc, humidity, pressure, windspeed, winddirection, sunrise, sunset, clouds, visibility) VALUES (\" + lat + \",\" + lon + \",'\" + time + \"','\" + city + \"','\" + weather + \"','\" + detail + \"',\" + tempc + \",\" + temp_maxc + \",\" + temp_minc + \",\" + humidity + \",\" + pressure + \",\" + windspeed + \",\" + winddirection + \",\" + sunrise + \",\" + sunset + \",\" + clouds + \",\" + visibility + \");\";\n\ndelete msg.location;\ndelete msg.payload;\ndelete msg.time;\ndelete msg.title;\ndelete msg.description;\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":500,"y":160,"wires":[["6b7816af.3e8918","abaa7692.9c1d08"]]},{"id":"6b7816af.3e8918","type":"debug","z":"71cd58d1.e9a748","name":"openweathermap to db","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":520,"y":240,"wires":[]},{"id":"abaa7692.9c1d08","type":"link out","z":"71cd58d1.e9a748","name":"openweathermap","links":["678f136d.70fa4c","8f5157e8.9523a8"],"x":665,"y":160,"wires":[]},{"id":"678f136d.70fa4c","type":"link in","z":"71cd58d1.e9a748","name":"openweathermap","links":["abaa7692.9c1d08"],"x":745,"y":160,"wires":[[]]},{"id":"c618e04f.0d1ae","type":"inject","z":"71cd58d1.e9a748","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":520,"wires":[[]]}]

Z = Zulu = UTC = GMT - they are all the same.

The other way to indicate Zulu time is "+0000" but that isn't the international standard.

I did think so they are all the same, but for some reason the db don't think so.

( I do have other insert statements in the same node-red instance writing to the same db Z times and they work, all the tables in the db are setup the same)

Show us the code building the query.

var lat = msg.data.coord.lat;
var lon = msg.data.coord.lon;
var time = msg.time;
var city = msg.data.name;
var weather = msg.payload.weather; //rain,sun
var detail = msg.payload.detail; //light rain
var tempc = msg.payload.tempc; //current temp
var temp_maxc = msg.payload.temp_maxc; //predicted max temp
var temp_minc = msg.payload.temp_minc; //predicted min temp
var humidity = msg.payload.humidity; //predicted humidity
var pressure = msg.payload.pressure;
var windspeed = msg.payload.windspeed;
var winddirection = msg.payload.winddirection;
var sunrise = msg.payload.sunrise;
var sunset = msg.payload.sunset;
var clouds = msg.payload.clouds;
var visibility = msg.data.visibility;
msg.topic = "INSERT INTO weathermap_t(lat, lon, time, city, weather, detail, tempc, temp_maxc, temp_minc, humidity, pressure, windspeed, winddirection, sunrise, sunset, clouds, visibility) VALUES (" + lat + "," + lon + ",'" + time + "','" + city + "','" + weather + "','" + detail + "'," + tempc + "," + temp_maxc + "," + temp_minc + "," + humidity + "," + pressure + "," + windspeed + "," + winddirection + "," + sunrise + "," + sunset + "," + clouds + "," + visibility + ");";

The time value coming in is a javascript Date object. By inserting into a string like that you have not given it any clues on how to format it so it has used Date.toString() which has given you a human readable date, but that is likely not what mysql wants. Try
let time = msg.time.toISOString()

You should be using let rather than var, it doesn't matter here but it is a good habit to get into.

1 Like

Thank you,

How do you adjust to GMT + 02:00?

Why? Does mysql not accept UTC timestamps?

[Edit] Also what column type are you writing it to?

You dont (or rather you shouldnt).

It is wise to store time in a database as UTC then only upon presentation do you manipulate the output (and even then this is usually not necessary as the end device usually presents the correct date/time to the user based on the users timezone setting). It is sometimes a bit different for a web page (depending on how you present the data to it) but that can be handled.

If you store datetime in local format, you will suffer in other areas e.g. when DST kicks in or a when a user from another timezone views it or you send the data to another another system that requires UTC (or worse still, they expect a different time offset).

the best option is store as UTC.

A workaround is store 2 times in your DB. UTC and local.

I have just been doing a bit of reading, and it appears to be saying that for mysql TIMESTAMP columns you have to provide the timestamp in local time and will convert to UTC to store it. Apparently you can't give it an ISO timestamp with timezone. I find that difficult to believe.

This seems to give some useful information on the subject:

MySQL Set UTC time as default timestamp - Database Administrators Stack Exchange

Oh, and worth remembering that in SQL, the standard date/time format is "YYYY-MM-DD HH:mm:ss" and not the ISO format "YYYY-MM-DDTHH:mm:ss.sssZ" format. In other words, SQL does not take any zone information. However, there is a function to ensure that input date/time is converted correctly.

The db is RDS in AWS so I am not sure if I can set the server time to GMT+02:00

So now for the arithmetic of the date + 02:00?

Google found this for me. Your incoming date is a javascript Date object so it should be easy to adapt. add or subtract timezone difference to javascript Date - Stack Overflow

1 Like

Thank you.

let date = msg.payload[0].tl_timestamp.toISOString();

var targetTime = new Date(date);
var timeZoneFromDB = 2; //time zone value from database
//get the timezone offset from local time in minutes
var tzDifference = timeZoneFromDB * 60 + targetTime.getTimezoneOffset();
//convert the offset to milliseconds, add to targetTime, and make a new Date
var offsetTime = new Date(targetTime.getTime() + tzDifference * 60 * 1000);

msg.payload = {
"time":date,
"offsetTime":offsetTime

}

There is no point converting it to a string and back again. You can replace those two lines with

let targetTime = msg.payload[0].tl_timestamp
1 Like

let targetTime = msg.payload[0].tl_timestamp;

let offset = 2; //time zone offset in hours

//get the timezone offset from local time in minutes
let tzDifference = offset * 60 + targetTime.getTimezoneOffset();

//convert the offset to milliseconds, add to targetTime, and make a new Date
let offsetTime = new Date(targetTime.getTime() + tzDifference * 60 * 1000);

msg.payload = {
"time":targetTime,
"offsetTime":offsetTime

}

The catch now is the time is correct (sort off) but it is taking it as Z time.

I wanted to correct the time, as to display it in worldmap.

image

@dceejay

Are you reading it back from mysql to feed to the map?

I read it from MYSql in AWS add the 2 hours and feed to map.

I thought you were adding 2 hours before putting it into mysql, in which case you should be taking it off again when you read it out to it back to UTC