November 22, 2017
December 18, 2017
Oh yes. This kind of thing can be massively confusing, and also largely inconvenient for the new user.
It would be nice for ThingSpeak to have a data DOWNLOAD option that has a time-zone drop-down menu that allows users to download the data file matched to the user's local time-zone.
But there appear to be no such feature at the moment.
So, at the moment, the date-time column will be in some kind of standard time form---- ie. "UTC" time, and the downloaded time values will be referenced to "0". Yes.... "zero".
So what you need to do is to use Google. And if you happen to live in say Tokyo, then google "Tokyo UTC". The google result will say something like "GMT+9" for Tokyo. Note the 'plus 9'. This means you need to add 9 hours to the time values that you see in your downloaded ThingSpeak file.
ThingSpeak typically gives you a 'CSV' file. So what you need to do is to open this CSV file using microsoft EXCEL. And then you need to SAVE that excel file to some other format, such as .xlsx format. This let's you take advantage of being able to use excel formulas to convert ThingSpeak date-time information to something that Excel can really use.
Eg. you might see this in cell A2: 2017-12-17 13:30:01 UTC
If you have a blank column, such as column M, then in cell M2, you enter a formula (into cell M2), such as: = LEFT(A2, LEN(A2) - 4)
Cell M2 will then contain: 2017-12-17 13:30:01
Then, in cell N2, type this formula: =M2 + 9/24
The important value is the '9' in the above line. The '9' makes excel add the 9 hours to get a time code linked to Tokyo's local time.
Then, in cell O2, type the same formula manually into cell O2: =M2 + 9/24
Also, do the same for cell P2. Type the same formula manually into cell P2: =M2 + 9/24
Here, the values in column M, N, and O will all be the same. This is where you can play around with the column formatting.
For example, if you highlight column N, and then choose to format the cells in column N to a "Time" format. And if you also choose the "Type" of time to be twelve-hour format (ie. AM/PM), then column N will be converted by excel time-formatting to 12 hour AM/PM format.
And, for column O, you could format the column to be "Time", and choose the "Type" to be 13:22:00 format, which is 24-hour format.
Both columns N and column O will be in Tokyo local time, which is 9 hours ahead of the ZERO UMT (or GMT) reference time.
There is an API you can use to retrieve all the data in a channel:
List of valid timezone entries is here. For example, to get the data in New York timezone, set TIMEZONE to America%2FNew_York, or to get the same in Mumbai set TIMEZONE to Asia%2FMumbai
Most Users Ever Online: 114
Currently Online: Hans, agrisensezm
Currently Browsing this Page:
Guest Posters: 1
Newest Members:Archieter, JamesExedo, IrbosHich, brodi, kasko, franklin castro
Administrators: Hans: 387, lee: 457