Time zone | ThingSpeak API | Forum

Avatar

Please consider registering
Guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed sp_TopicIcon
Time zone
No permission to create posts
November 22, 2017
3:21 pm
Avatar
peter9x
New Member
Members
Forum Posts: 2
Member Since:
November 22, 2017
sp_UserOfflineSmall Offline

Hello everybody. When I got data export, I realized my time's message was not matched with my time zone. For instance, if my time is 15:00 PM, I only recieved a message from 7:50 AM. Do you have any ideas? Have a nice day.

November 27, 2017
8:39 pm
Avatar
Vinod

MathWorks
Members
Forum Posts: 201
Member Since:
May 1, 2016
sp_UserOfflineSmall Offline

The data exported from ThingSpeak using the 'Data Import/Export' tab is in UTC. When analyzing this data offline, you will need to add the timezone offset from your timezone to UTC to the data.

December 18, 2017
4:11 am
Avatar
SouthPark
New Member
Members
Forum Posts: 4
Member Since:
December 18, 2017
sp_UserOfflineSmall Offline

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.

December 29, 2017
2:40 pm
Avatar
Vinod

MathWorks
Members
Forum Posts: 201
Member Since:
May 1, 2016
sp_UserOfflineSmall Offline

There is an API you can use to retrieve all the data in a channel:

https://api.thingspeak.com/stream/channels/CHANNEL_ID/feeds?api_key=READ_API_KEY&timezone=TIMEZONE

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 

Forum Timezone: America/New_York

Most Users Ever Online: 114

Currently Online: Hans, agrisensezm
38 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

rw950431: 261

Vinod: 196

piajola: 85

turgo: 70

vespapierre: 63

Adarsh_Murthy: 62

Member Stats:

Guest Posters: 1

Members: 5703

Moderators: 0

Admins: 2

Forum Stats:

Groups: 4

Forums: 17

Topics: 1313

Posts: 4565

Newest Members:

Archieter, JamesExedo, IrbosHich, brodi, kasko, franklin castro

Administrators: Hans: 387, lee: 457