Time zone | ThingSpeak API | Forum

Avatar

Please consider registering
Guest

Search

— Forum Scope —






— Match —





— Forum Options —





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

Register Lost password?
sp_Feed sp_TopicIcon
Time zone
Avatar
peter9x
New Member
Forum Posts: 2
sp_UserOfflineSmall Offline
1
November 22, 2017 - 3:21 pm
sp_Permalink sp_Print

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.

Avatar
Vinod

Forum Posts: 186
sp_UserOfflineSmall Offline
2
November 27, 2017 - 8:39 pm
sp_Permalink sp_Print

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.

Avatar
SouthPark
New Member
Forum Posts: 4
sp_UserOfflineSmall Offline
3
December 18, 2017 - 4:11 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Vinod

Forum Posts: 186
sp_UserOfflineSmall Offline
4
December 29, 2017 - 2:40 pm
sp_Permalink sp_Print sp_EditHistory

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:
21 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

rw950431: 252

Vinod: 164

cstapels: 96

piajola: 75

vespapierre: 63

Adarsh_Murthy: 59

Newest Members:

dannymm1

KamorkasOr

GRV2903

Jusikaclaiz

Raymondpoers

ScottDus

Forum Stats:

Groups: 4

Forums: 17

Topics: 1313

Posts: 4565

 

Member Stats:

Guest Posters: 1

Members: 5703

Moderators: 0

Admins: 2

Administrators: Hans, lee