2 ms·

Ok here's a little bit of a guilty confession.... we _love_ MySQL's `timestamp null` type. MySQL certainly has some areas that aren't pretty, but we use the hel

Ok here's a little bit of a guilty confession.... we _love_ MySQL's `timestamp null` type. MySQL certainly has some areas that aren't pretty, but we use the hell of out this. The `timestamp null` time automatically adjusts for the connecting user's timezone. Internally, IIRC it stores the value as UTC, but then converts it on the fly for wherever you're sitting. Why is this awesome? Well, because not everyone is a programmer or expert in time zone, daylight savings, understands UTC, etc. When our non-tech CEO wants to look at a report, he expects it to be relative to the time shown on his watch, which is exactly what this type does. Is it perfect? No. But does it solve a simple problem eloquently and prevent non-technical users from making bone-headed mistakes? yep.

avalys 5y ago
That sounds horrible, unless the CEO is writing and executing SQL statements on his own, directly against the database. If I'm sitting in Seattle VPN'd into a network that geolocates to South Carolina, and I send an HTTP request that terminates on a front-end box in Texas and sends a SQL query to a database in Ireland, what timezone will be used in the result? And why is this better than just making the same calculation in the UI layer?
- exabrial 5y ago
  In that scenario, your browser communicates the timezone to the box in Texas, which sets the DB connection timezone to Seattle and the Irish database does the conversion for you. The VPN doesn't really factor in. Example: An order comes in 22:00 UTC, he's sitting in Seattle talking to a parts supplier. Said supplier want to know when the last order came in, so CEO pulls a sales report. It appears on the report as 3pm. This is intuitive and works every single time. Ironically, that scenario you describe would be quite complex to implement with a ui tweak. What if your data entry is a webapp, the CEO is using Metabase, and your data analyst is using Tableau? You know have to implement timezones 'correctly' in three different pieces of software, sometimes with software you didn't write.

avalys 5y ago

That sounds horrible, unless the CEO is writing and executing SQL statements on his own, directly against the database. If I'm sitting in Seattle VPN'd into a network that geolocates to South Carolina, and I send an HTTP request that terminates on a front-end box in Texas and sends a SQL query to a database in Ireland, what timezone will be used in the result? And why is this better than just making the same calculation in the UI layer?

exabrial 5y ago
In that scenario, your browser communicates the timezone to the box in Texas, which sets the DB connection timezone to Seattle and the Irish database does the conversion for you. The VPN doesn't really factor in. Example: An order comes in 22:00 UTC, he's sitting in Seattle talking to a parts supplier. Said supplier want to know when the last order came in, so CEO pulls a sales report. It appears on the report as 3pm. This is intuitive and works every single time. Ironically, that scenario you describe would be quite complex to implement with a ui tweak. What if your data entry is a webapp, the CEO is using Metabase, and your data analyst is using Tableau? You know have to implement timezones 'correctly' in three different pieces of software, sometimes with software you didn't write.