Comments (11)
Ah, interesting point. We should definitely agree on something to do here.
Personally, I'm of the opinion that we should keep using UTC in the API, and change the representation in the user's browser to their current timezone, which we can fetch via Javascript.
from air-quality-web.
There's DST to throw into the mix as well. Takes some thinking about.
from air-quality-web.
Perhaps the charts X-axis should say Time (UTC) instead of just Time. The dbLoader would need to know if the time includes DST and correct it - that'll be one for me then.
Still needs discussion, maybe this coming thursday.
from air-quality-web.
Can we clarify terminology - it is hard enough without adding confusion. I only refer to DST in the context of the USA time zones. I know it has been used in the UK to refer to BST sometimes. Just to keep things clear can we use UTC == GMT (either works for me) and BST. BST is GMT+1 (or UTC+1). Then drop DST?
from air-quality-web.
@BNNorman: It's actually much simpler than that. We don't actually need to care about daylight savings times, because we can do it all through the browser.
In JS, there's a method called .toLocaleString()
, which translates a date into the user's local time zone, automatically. To this end, we can run the data points through this before rendering.
- UTC - Universal Coordinated Time. We want to (and do, IIRC), store data in this format.
- GMT - Greenwhich Mean Time - not the same as UTC, but close enough for our purposes. See this article
- BST - British Summer Time. Definitely not the same as UTC or GMT. Basically +1 hours, and we use it in the summer time. We want data to display in this I suspect.
- DST - Daylight Savings Time. Not a time zone - actually an abbreviation.
from air-quality-web.
Sorry, should have used lower case to signify dst isn't a time zone - but it is a flag to show that date-times are 1 hour ahead of UTC.
I did find this...
When you insert a TIMESTAMP value into a table, MySQL converts it from your connection’s time zone to UTC for storage. When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection’s time zone. Notice that this conversion does not take place for other temporal data types such as DATETIME.
The original code for dbLoader didn't pass in the offset. It used string formatting to ignore it. We actually use the timestamp column type.
I wonder what effect this will have. It may mean all data plotted is plotted as BST even when it was GMT as we transition to BST. What fun. Time to experiment.
from air-quality-web.
Ooooh, ewww. That would be a nasty bug if so. If possible, we want all communications everywhere to be in UTC, and only convert when displaying it to the user. That should avoid confusion between devices & servers.
Looks like I might have to investigate setting the timezone of the connection. I didn't know about that - thanks!
from air-quality-web.
Mysql has a TZ_CONVERT(timestamp,timezone) function but it means storing the timezone in the database.
We could bury our group heads in the sand and see what happens when we get into BST. I suspect there would be a 1 hour gap in the data.
Then at the other end of the year readings that appear to be duplicates. Imagine before the switch back to GMT levels are low, then the day after they are high. I think the chart would show levels oscillating between low and high within the same hour. Chuckle.
from air-quality-web.
It should store the timezone information as part of a DATETIME
.
Either way I really think we should store data in the DB in UTC if we aren't already.
from air-quality-web.
No it doesn't - I tried it.
It stores the same value as a timestamp which is converted to utc according to @@session.time_zone.
timestamps are converted back, again also depending on @session.time_zone but datetime types are not.
With timestamps you can store values in the server's local time zone and read them back in a different timezone.
Yes we are storing in UTC. We will see what happens to the charts when we enter and leave BST.
I wonder what someone abroad looking at the charts will see. Will it appear to be their local time or ours?
For now I think we should close this and revisit if it is a problem.
from air-quality-web.
Ah, I see.
Sounds good to me! In that case, they should see it in UTC also.
Looks like we've still got some work to do with respect to date and time handling though. Ideally, it would be rather nice if we could store as a DATETIME
without any conversion to a timestamp, to avoid loosing the timezone tag - just in case.
from air-quality-web.
Related Issues (20)
- Display the currently selected reading type on the heatmap when loading the device graph HOT 1
- Migration: Slow HTTP API calls HOT 9
- When clicking sensors on the AQ Map (sometimes) the map just zooms HOT 5
- Sensor filter HOT 12
- Mosquitto stuck Address in use (1883) HOT 3
- FTW says 6 hours ago but last reading was 12:00 (now 16:00) HOT 13
- Issue with web site on some devices HOT 22
- Repeated measurement types above chart HOT 5
- Can the charts be made to open with data rather than just say error: no data recorded HOT 1
- Slow charts HOT 5
- POST request : Error 500 Internal Server Error HOT 7
- API action list-devices-near is not quite working - it returns only the same sensor HOT 8
- device.visible has no effect on the map HOT 7
- API and documents not working HOT 3
- Circles, on Opening the Map, don't disappear HOT 2
- Sensor not showing up correctly on current (0.14-dev) beta site HOT 5
- Last seen on chart is wrong HOT 6
- Wrong base URL in the API docs HOT 1
- graph not showing data for device bonus-a-abp HOT 5
- Integration with custom sensors and visual optimizations ? HOT 8
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from air-quality-web.