Further confirmation all dates are UTC millis, regardless of db timezone settings

(08:36:39) Russ: have a second?
(08:36:51) jamisbuck: sure
(08:36:57) Russ: do you use Mysql much?
(08:37:02) Russ: or Oracle?
(08:37:05) jamisbuck: mysql
(08:37:07) jamisbuck: I used oracle at byu
(08:37:17) Russ: great; done much with timestamps in mysql?
(08:37:22) jamisbuck: no, I avoid them
(08:37:26) jamisbuck: they don't act very intuitively
(08:37:36) Russ: intuitively...meaning?
(08:38:00) jamisbuck: updating the row automatically updates the timestamp, even if it was not explicitly requested
(08:38:03) jamisbuck: which goes counter to my way of thinking
(08:38:12) Russ: ah, right, only for the first column though ;)
(08:38:19) Russ: so, that aside
(08:38:45) Russ: do you explicitly set the timezone of your Mysql server to something other than the default SYSTEM?
(08:38:51) jamisbuck: no
(08:39:15) jamisbuck: we always store the times as utc, and explicitly transform them ourselves to the customer's time zone on display
(08:39:25) Russ: okay, that's exactly what I wanted to chat with you about
(08:39:30) Russ: the concept of storing them as UTC
(08:39:52) Russ: isn't *any* timestamp (or date/datetime for that matter) stored in UTC as millis from the epoch?
(08:40:26) jamisbuck: I have no idea :( but I'm pretty sure mysql dates/datetimes aren't limited to the epoch
(08:40:44) Russ: correct, they're not, the negative values work retroactively
(08:41:49) Russ: I'm struggling with storing time as UTC. Given a Calendar object in Java for today 9 Nov 00:00:00.000 GMT, I can store that in the database as my timestamp and verify the millis going in are the millis coming out--which is what I would expect if the date is indeed stored in UTC.
(08:42:07) Russ: The kicker is, any time I view that date via mysql console (or otherwise), the String representation is MST
(08:42:15) Russ: that is, 8 Nov 17:00:00:00.000
(08:42:30) Russ: not such a big deal, except that it can be confusing to convince that the date is correctly stored
(08:42:35) Russ: when not evaluating the millis
(08:42:35) jamisbuck: what is the timezone for your system?
(08:42:39) Russ: MST7MDT
(08:42:43) jamisbuck: yah, ours is utc
(08:42:52) Russ: I even changed that
(08:42:53) jamisbuck: I bet mysql is converting the date to the system's zone on output
(08:42:57) Russ: that's what I'm thinking
(08:43:10) Russ: I changed everything I could find, *except* my local timezone, and I get the same result
(08:43:20) Russ: even remote connections, from a different time zone
(08:43:36) Russ: which again points to the mysql server on my box "translating" the appropriate time
(08:43:49) jamisbuck: right
(08:43:50) Russ: so, theoretically, if I change my system timezone to UTC, I'll see the String I expect?
(08:44:01) jamisbuck: let me check something quick
(08:44:18) Russ: thanks for your input on this, I've been about on the verge of insanity of verification of this
(08:45:16) Russ: and the only remaining reason is that my system timezone is involved in the String representation
(08:45:31) jamisbuck: ok, I take it back, our servers have CST as the time zone, and mysql is configured to use SYSTEM as the time zone
(08:45:41) jamisbuck: but when I select a date via the mysql console, it doesn't show me any TZ information
(08:45:46) Russ: correct
(08:45:50) jamisbuck: 2004-04-11 07:07:37
(08:46:06) Russ: which if you get the millis, and view the UTC representation, will be the offset of CST
(08:46:22) jamisbuck: k, well, it has never bitten us in our rails apps
(08:46:29) jamisbuck: the times go in and come out correctly
(08:46:32) Russ: exactly
(08:46:41) Russ: as long as you use the millis, and not the string reported, that should be the case
(08:46:52) jamisbuck: to be honest, I use script/console more than I use mysql
(08:47:14) Russ: don't follow...you mean the console mysql executable?
(08:47:34) jamisbuck: I use my rails' app's script/console, istead of the mysql client program
(08:47:37) Russ: gotcha
(08:48:15) Russ: well thanks a bunch for your confirmation--my expectation is that whatever driver is managing the connection and result sets returns the millis any time a date/time/timestamp is retrieved
(08:48:37) Russ: the String representation is the secondary, because of the TZ offset calculations
(08:48:47) jamisbuck: alright, glad I could help
(08:48:48) Russ: which brings me around to, why set the timezone on a database?
(08:49:25) jamisbuck: I seem to remember that you can set some properties on the client connection. Maybe some of those specify how to deal with time zones
(08:49:38) jamisbuck: but it doesn't seem like it would help much if you are dealing with dates in multiple time zones
(08:49:44) Russ: exactly
(08:50:00) Russ: in Java you can muck with the millis going in if you provide the connection timezone
(08:50:18) Russ: and the JDBC driver will essentially "translate" the GMT millis to represent the timezone equivalent
(08:50:20) Russ: if that makes sense
(08:50:24) jamisbuck: yah
(08:50:29) Russ: anyhow, thanks again
(08:50:38) Russ: I wondered how Ruby dealt with it
(08:50:50) Russ: and it you've definitely confirmed my expectation
(08:50:52) Russ: have a good one!
(08:50:57) jamisbuck: you too!