Either this or I'm posting in my sleep

November 4, 2012 1:21 AM

My comment in the Catcher-in-The-Rye thread is showing up with an earlier time stamp than comments ahead of it. I can read everything fine, just wondering if it's A Daylights Savings Time thing or if I/the mods should be concerned.
posted by EmpressCallipygos to Bugs at 1:21 AM (59 comments total)

HOW DEEP DOES THIS GO.

(It is clearly Daylight Savings Time. Comments go from 1:59am to 1am.)
posted by OnTheLastCastle at 1:27 AM on November 4, 2012

Yeah, I'm pretty sure it's a time change artifact. Pb can let us know about that.
posted by taz (staff) at 1:33 AM on November 4, 2012

That's nothing, I am posting from the future!

relative to your post
posted by zippy at 1:56 AM on November 4, 2012

What is especially interesting -- the five-minute edit window on my comment opened back up again an hour later!
posted by Harvey Kilobit at 2:06 AM on November 4, 2012 [9 favorites]

....the five-minute edit window on my comment opened back up again an hour later...

brb, writing next summer's blockbuster SF murder mystery
posted by DU at 2:09 AM on November 4, 2012 [3 favorites]

heh. There an edit window in the space-time continuum!
posted by taz (staff) at 2:16 AM on November 4, 2012 [14 favorites]

I was looking at my computer earlier wondering if it was Daylight Savings 2 am or Standard Time 2 am. I had sort of lost track of time. Looking at timestamps in that Metafilter thread is what answered the question for me.
posted by litlnemo at 3:46 AM on November 4, 2012

Can someone commenting from the future let us know the result of the US presidential election?
posted by Area Man at 5:05 AM on November 4, 2012

And this, friends, is why you should always store your times in UTC, not local time.

Yes, these problems are related to the fact that we store all comment and post times in Pacific Time and we had a time change on the server last night. This is on our list of things to change someday, but once you've stored times this way for over ten years, it's a big project to historically calculate all of these daylight savings time shifts and translate the existing dates to UTC.
posted by pb (staff) at 5:16 AM on November 4, 2012 [4 favorites]

Can someone commenting from the future let us know the result of the US presidential election?

Sure. Chelsea Clinton beat incumbent Paul Ryan in a landslide.
posted by googly at 5:19 AM on November 4, 2012 [10 favorites]

1. Go back in time
2. Abuse edit window
3. ...
4. Profit?

I may be doing this wrong.
posted by arcticseal at 6:10 AM on November 4, 2012

Go back to bed Americans, you have another hour today.
posted by jessamyn (staff) at 6:47 AM on November 4, 2012 [4 favorites]

Cats hopped on me until I woke up. Cats are not interested in this "extra" hour. Cats are interested only in breakfast, which is to be served at the the correct time, regardless of what time it is.
posted by rtha at 6:52 AM on November 4, 2012 [20 favorites]

Go back to bed Americans, you have another hour today.

Yeah, sure, easy for you to say... you tell the Husky to go back to bed, see how well that works...
posted by HuronBob at 6:58 AM on November 4, 2012

This is on our list of things to change someday, but once you've stored times this way for over ten years, it's a big project to historically calculate all of these daylight savings time shifts and translate the existing dates to UTC.

Plus it's more fun this way. If we have to have this stupid DST system in the US, we might as well at least sow confusion among the masses a little.
posted by cortex (staff) at 7:51 AM on November 4, 2012

it's a big project to historically calculate all of these daylight savings time shifts and translate the existing dates to UTC.

Challenge Mode: One SQL statement
posted by DU at 8:09 AM on November 4, 2012 [1 favorite]

I am trying to come up with a movie thriller whose plot hinges on the reappearance of the edit window an hour after a comment was originally made. Hmmm...
posted by ocherdraco at 8:11 AM on November 4, 2012

I overslept this morning.
It is not easy to oversleep when the US Congress has given one an extra hour of sleep.
I am an overachiever that way
posted by SLC Mom at 8:20 AM on November 4, 2012 [2 favorites]

Listen.

MetaTalk has become unstuck in time.
posted by The Whelk at 8:28 AM on November 4, 2012 [10 favorites]

UPDATE comments
SET timestamp = CONVERT_TZ(timestamp, 'PST','UTC');

if mysql is the backend. Postgres may allow a view that translates for you.
posted by jenkinsEar at 8:57 AM on November 4, 2012

If you have a timestamp at 2:01am for one comment, and a timestamp of 2:01am for another comment that happened an hour later because of a DST shift, how is CONVERT_TZ smart enough to know which UTC hour it should be converted to?
posted by pb (staff) at 9:12 AM on November 4, 2012

I guess it doesn't matter, does it? We can't fix those anyway.

We're using SQL Server, but there are probably some timezone tools there too.
posted by pb (staff) at 9:13 AM on November 4, 2012

When they introduced DST in Mexico people were not happy.

Over 10 years later there are still many conversations that go like this:

- See you tomorrow at 3:30.
- 3:30 God's time or 3:30 government time?

I love these opportunities to ask if by God's time they mean leap seconds, the gradual slowing of the Earth's rotation until it stops and one hemisphere is permanently cooked while nitrogen rains out of the sky in the other one, and the inevitable destruction of the planet once the Sun evolves into a red giant and the Earth boils into space. Old catholic ladies love me.
posted by Doroteo Arango II at 9:14 AM on November 4, 2012 [5 favorites]

This happened to me last year, I believe I could dig up a panicked email to Jess or some such on "eek! something fishy just happened"
posted by infini at 9:16 AM on November 4, 2012

"If you have a timestamp at 2:01am for one comment, and a timestamp of 2:01am for another comment that happened an hour later because of a DST shift, how is CONVERT_TZ smart enough to know which UTC hour it should be converted to?"

Isn't time just stored as a long integer and then formatted when viewed? So, to me, this would mean that the first 2:01AM (before the time change) would be an integer like 12345. Then, the second comment of 2:01AM (after the time change) would be an integer like 12405 (because it is 60 minutes later). So the integer is still bigger, it is just represented differently to the viewer.

Or, that's how I would expect my Informix SQL databases to work. But if you store it as a character string, then you're fucked. LOL.
posted by jillithd at 9:18 AM on November 4, 2012

One advantage is that I woke up early enough to see the Abu Dhabi F1 race, and so thanks for DST!
posted by arcticseal at 9:19 AM on November 4, 2012

We can't fix those anyway.

Couldn't you spot clock changes by looking for comment pairs which are consecutive by comment ID and out of order by timestamp, then use that information to recover DST status for individual comments? But maybe the comment volume wasn't always high enough for the clock change to leave traces like this.
posted by stebulus at 9:29 AM on November 4, 2012

CONVERT_TZ(timestamp, 'PST','UTC');

But isn't the whole problem that some timestamps are PST and some are PDT, and the data doesn't say which?
posted by stebulus at 9:33 AM on November 4, 2012

Speaking of things to change, I'm sure you are aware that some countries switch to and from DST at a different time than the U.S. or don't switch at all. This means the fixed time offset you can select in your profile will be off for at least some part of the year. It's something I guess you would solve by using a time zone database and redoing the option in the user profile--so a lot of work to fix what isn't that big of annoyance, at least to me. But I just wanted to make sure it's on the list. Isn't it?
posted by tykky at 9:45 AM on November 4, 2012

I still think we need "MetaFilter Standard Time," calculated in number of seconds since the first post, then everyone can just convert it to their preferred calendrical system. Easy and flexible!

Curiously, Firefox does not like "calendrical" and suggests "cylindrical," "calender," "calendar," and "anticlerical." For some reason, I find this amusing. Perhaps it's the extra sleep.
posted by GenjiandProust at 10:26 AM on November 4, 2012 [1 favorite]

To follow stebulus' comments: as long as you have a comment in the "first" 1-2 AM hour (say, at 1:45 PDT) and a comment that's earlier by the clock but later chronologically (say, at 1:15 PST) you'd be okay; you could say that the change happened between those two.

The chances of that happening are... um... an increasing function of comment volume. I don't know which one because I am lazy.
posted by madcaptenor at 10:36 AM on November 4, 2012

Isn't time just stored as a long integer and then formatted when viewed?

No, it's stored as a SQL Server date/time entry.

But isn't the whole problem that some timestamps are PST and some are PDT, and the data doesn't say which?

Yeah, that's the problem. We might have to call those a lost cause and fix things going forward.

But I just wanted to make sure it's on the list. Isn't it?

Yep, in my perfect world that's what we'd do: convert everything to UTC and set timezone in prefs instead of offsets. It's a fairly big project and we haven't felt like it was worth fixing these little annoyances yet.
posted by pb (staff) at 10:38 AM on November 4, 2012

...Timey-Wimey, Wibbly-Wobbly....
posted by zarq at 10:38 AM on November 4, 2012 [1 favorite]

Then possibly it could work like this?

1) Assuming all timestamps are Pacific and there are no quirks in addition to DST: figure out which ones are PST and which PDT and convert them to UTC (Unix time) accordingly. If it proves impossible to distinguish PST from PDT retroactively from the string values for contributions around the changeover times (example from this morning), maybe do this for new contributions only, e.g. starting January 1, 2013? Either way, store both "legacy" and "Unix time" values in the db for each contribution.

2) Make these play nice with the public tz database and test against quirk cases such as the semiannual DST window between the U.S. and EU, and weird-ass timezones like Nepal and the Chatham Islands.

3) Make this an explicitly user-selectable option on profiles: "Use UTC for timestamps? ☑": the default is continued use of the legacy offset to ensure backward compatibility and transparency. (If not retroactive, notify users via pop-up or mouseover when selecting UTC that contributions before 2013 will still show the legacy offset which may not always be accurate for their timezone.)

4) Nobel Peace Prize for pb.

Am I overlooking something?
posted by goodnewsfortheinsane (staff) at 10:39 AM on November 4, 2012

Lying in my bed I hear the clock tick
I've been around enough to know
Time is never time at all
Have no time to look around
If I could find a way
In time it could have been so much more
Years go falling in the fading light
And then one day you find
When the clock strikes twelve
All your money won't another minute buy
posted by Meatbomb at 10:45 AM on November 4, 2012

This is more viral marketing for Shakespearian 's choose your own adventure story isn't it?
posted by The Whelk at 10:46 AM on November 4, 2012 [2 favorites]

Am I overlooking something?

Continuing with two timestamp columns wouldn't be a great option. Our tables are highly indexed—especially for things like Recent Activity where we combine data from dozens of tables. Having separate queries for timestamp vs. UTC folks would be an incredible headache behind the scenes.

So if we decide this is a priority, we will make the date storage change behind the scenes, re-optimize everything for the new values, and make that the standard. We could either guess on a timezone based on the offset, or ask people to explicitly set a timezone the next time they log in. Again, this hasn't been a priority for us in the past. These are small hassles to put up with.

If I was starting MetaFilter today instead of over 12 years ago I'd definitely do things differently—especially since time is central to everything here. It's kind of like putting in a new foundation. There's a lot of stuff built up on top of the way things are.
posted by pb (staff) at 10:47 AM on November 4, 2012 [2 favorites]

Time can never mend...
posted by infini at 10:58 AM on November 4, 2012

Ah, that makes sense. FWIW it's not a priority for me personally: I know I've brought it up here in the past and yes, it can be a bit confusing in the spring/fall U.S./EU DST window for us Euros. But I'm just happy it's being discussed.
posted by goodnewsfortheinsane (staff) at 11:02 AM on November 4, 2012

Oh, alright, I got nerd-sniped.
posted by madcaptenor at 11:24 AM on November 4, 2012 [3 favorites]

Just a reminder that there's only 1 saving in Daylight Saving Time.
Not sure what it is that's saved, but it's only only one thing.

I work on a project that does Phase I Clinical Trial scheduling. DST is a major headache. The head of the group wants to petition congress to get rid of DST.
posted by MtDewd at 11:38 AM on November 4, 2012 [1 favorite]

Could we as a group take on the task of abolishing the stupidity of Daylight Saving Time? What could we do? Write Congresspersons, and the newly re-elected President?
posted by Cranberry at 11:42 AM on November 4, 2012

Cats hopped on me until I woke up. Cats are not interested in this "extra" hour. Cats are interested only in breakfast, which is to be served at the the correct time, regardless of what time it is.

Pictures of the suspects please?
posted by discopolo at 12:21 PM on November 4, 2012 [2 favorites]

There is nothing wrong with this time of the year! This is Standard Time, baby. The time which sets the standard for all other times to follow.

This, right now, is the best time there is! I wake up in the morning and I have a whole 'nother hour to lounge around in bed before everyone around here even gets to complain about how come I am lounging around in bed when the sun is up already?! That's awesome.

Spring, now....don't even get me started on Spring! You can call it Daylight Savings Time all you want, but that's just political doublespeak. In Spring, an hour of my life is just stolen away from me for no good reason. I walk around feeling like a zombie until I get acclimated to the Time Thief's schedule.

Where does that hour go? That hour isn't "saved"! I don't have that hour sitting around for a rainy day, when I can take it out and play around with it. I don't get any interest on that hour. I don't see it paying off later in dividend hours I've earned, hours I can waste having sex, eating delicious food, reading a novel or napping under my blankets.

No, that hour just goes out in the wild somewhere and parties its ass off until, come November, in the wee hours of the morning, it stumbles home to me in the dark, like a shamefaced drunk after a particularly nasty, blackout-inducing binge, attaching itself to my digital clock without so much as an apology for the inconvenience it's caused. Like I won't even notice the difference! Like I didn't even miss it while it was gone, and yearn for its return.

Still, I welcome it back, rejoicing that the prodigal 60 minutes have returned to me, and all is forgiven. Life is as it should be

Until Spring, when it falls right back off the wagon again.


Come back here next Spring and complain about the time change then. Come see me after the vernal equinox, 'round about the second Sunday in March, and we'll commiserate. I'll be griping right along with you.

Right now, though? La la lalala, I can't hear you, I got my hour back!
posted by misha at 12:32 PM on November 4, 2012 [5 favorites]

Suspect Number One

Suspect Number Two

Please note that suspect #1 did most of the actual hopping, poking, nosing, rustling, etc. before finally worming under the covers and lying down....on my bladder; suspect #2 was responsible for a small amount of prodding before curling up and going to sleep...in a way that made it very difficult to get out of bed, which #1 was insisting on.
posted by rtha at 12:43 PM on November 4, 2012 [2 favorites]

Looking GLASS... through the... WE ARE people...
posted by blue_beetle at 12:44 PM on November 4, 2012

pb: "Yeah, that's the problem. We might have to call those a lost cause and fix things going forward."

Yes, please!! MetaFilter is the only website where I have to go into my profile and change my time zone twice a year, despite my time zone not actually changing.
posted by Bugbread at 5:56 PM on November 4, 2012

Isn't time just stored as a long integer and then formatted when viewed?

No, it's stored as a SQL Server date/time entry.

Erm, yes, which in my experience is really just a fancily handled long integer. At least in my ESQL/C code, we select a date from the database into a long integer in the code to do whatever it is we need to do, and then insert the long integer back into the SQL date field.

But that's *my* code/environment and I don't know MeFi's code/environment, so... :)
posted by jillithd at 7:50 PM on November 4, 2012

Erm, yes, which in my experience is really just a fancily handled long integer.

It sounds like you're handling dates properly. The long integer probably corresponds with UTC. SQL Server does store dates/times as two integers. According to this source, "The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second." So as midnight on the server shifts, so do the integers.
posted by pb (staff) at 8:27 PM on November 4, 2012

I thought the Mayan calendar renders this a moot point in a month or so.
posted by JohnnyGunn at 9:35 PM on November 4, 2012

Bugbread writes "MetaFilter is the only website where I have to go into my profile and change my time zone twice a year, despite my time zone not actually changing."

There is a simple solution to this problem: move to a geographical location that is in sync with California time. All the cool kids are doing it.

Also I vote for just assigning the dates as displayed in historical threads and not messing with conversion. After all that's what is happening now so you wouldn't be changing anything and does it really matter whether a comment made years ago in a very narrow window is off by an hour? No.
posted by Mitheral at 10:20 PM on November 4, 2012

After all that's what is happening now so you wouldn't be changing anything and does it really matter whether a comment made years ago in a very narrow window is off by an hour? No.

I wouldn't risk it. Some day, in the years ahead, that accuracy might save the world. Or some such.
posted by infini at 10:26 PM on November 4, 2012

In 1999, people scoffed about the effect of minor issues in the way these things are recorded and look what happened then!
posted by dg at 10:52 PM on November 4, 2012 [1 favorite]

Couldn't you spot clock changes by looking for comment pairs which are consecutive by comment ID and out of order by timestamp, then use that information to recover DST status for individual comments?

I was resisting going to sleep, so I tried this on the commentdata_mefi file in the infodump. Together with the salient missing hours in the spring, and information from a couple MeTa posts, it's possible to reconstruct most of the time changes for Metafilter comments:

• 1999-10-30: DST ends between comments 59 and 60 (presumed)
• 2000-04-02: DST starts between comments 2635 and 2636 (presumed)
• 2000-10-29: DST ends between comments 26401 and 26402
• 2001-04-01: DST starts between comments 64124 and 64125
• 2001-06-04: Three comments (89175, -7, -9) are backdated to February and March; thus by comment ID they appear to be in DST, but by timestamp they are in Standard Time.
• 2001-10-28: DST ends between comments 164173 and 164174
• 2002-04-07: DST starts between comments 255837 and 255838
• 2002-10-27: DST ends between comments 375024 and 375025
• 2003-04-06: DST starts between comments 468291 and 468292
• 2003-10-26: DST ends between comments 574441 and 574442
• 2004-04-04: DST starts between comments 648931 and 648932
• 2004-10-31: DST ends somewhere among comments 759282, -3, -4, -5, -6, -7, which all have timestamps between 01:00 and 02:00, and are all in order, so I see no way to tell how many were in the first 1-2am and how many in the second.
• 2005-04-03: DST starts between comments 895710 and 895711
• 2005-10-30: DST ends between comments 1089545 and 1089546
• 2006-04-02: DST starts between comments 1265343 and 1265344
• 2006-10-29: DST ends between comments 1477175 and 1477176
• 2007-03-08: In accordance with new legislation, American DST now starts on the second Sunday of March (instead of the first Sunday of April, as heretofore). The Metafilter server hasn't heard the news and stays on Standard Time. A MeTa is opened three days later, to no effect.
• 2007-04-01: Metafilter DST starts, following the old rules, between comments 1638818 and 1638819
• 2007-10-28: The end of American DST is now the first Sunday in November (not the last Sunday in October as heretofore), but the Metafilter server still hasn't heard the news. So, Metafilter DST ends now, a week early, between comments 1890260 and 1890261. No MeTa is opened, but Metafilter returns to DST, apparently by manual intervention, at 11:05 (actual time; 10:05 by the server's prematurely de-DST'ed time), between comments 1890508 (10:05:23.080) and 1890509 (11:06:53.590).
• 2007-11-04: DST ends, both for the Metafilter server and for other residents of the US, following the new rules, between comments 1899392 and 1899393 (which were actually in the same thread, so you can see apparently out-of-order comments right beside each other, just as in the example that started this MeTa).
• 2008-03-09: DST does not start at 2am as it should; a MeTa is opened; pb manually starts DST around lunchtime, between comments 2039252 and 2039253
• 2008-11-02: DST ends between comments 2321988 and 2321989
• 2009-03-08: DST starts between comments 2479230 and 2479231
• 2009-11-01: DST ends between comments 2804887 and 2804888
• 2010-03-14: DST starts between comments 2992717 and 2992718
• 2010-11-07: DST ends between comments 3363259 and 3363260
• 2011-03-13: DST starts between comments 3574070 and 3574071
• 2011-11-06: DST ends between comments 4014301 and 4014302
• 2012-03-11: DST starts between comments 4233977 and 4233978

I fear the other subsites don't have the comment volume to support this kind of reconstruction.

does it really matter whether a comment made years ago in a very narrow window is off by an hour? No.

You have your opinion, sir, and, well, I guess it's pretty sensible.
posted by stebulus at 12:39 AM on November 5, 2012 [3 favorites]

Hm. Actually 2007 was more complicated than I thought. There was some downtime knocking out most of 2007-03-25, and the new server(s) came online with the wrong time zone, which mathowie fixed a few hours later. So, comments 1632927 through 1632947 should have an hour subtracted from their timestamps to be correct Metafilter Time. (Maybe the new servers knew the new-as-of-2007 DST rules and had to be educated in our ways?)
posted by stebulus at 1:13 AM on November 5, 2012 [1 favorite]

Is it wrong that I've spent about half of my favorites ration on this thread? It doesn't feel wrong, but it can be hard to tell sometimes.
posted by SMPA at 4:37 AM on November 5, 2012

I am not a big far of Daylight Saving Time. It pretty much serves to a) increase the length of period when the rising sun stabs me in the eyes during my commute by something close to 2 months, and it means in the winter I spend more time walking to work in the light (which I do not care about) and walking home in the dark (which kind of annoys me). Strangely, I really enjoyed being in Reykjavik in January, where the sun barely bothers to come up, assuming you could see through the clouds, which you can't.
posted by GenjiandProust at 5:27 AM on November 5, 2012

Continuing on stebulus' fine work:

2012-11-04: DST ends between comments 4660956 and 4660957

One thing we found here in our Clinical Trials scheduling is that when the server and the client are in different time zones, the fall time change messes up twice. In fact, if the client is 1 hr east of the server, three events scheduled an hour apart can all show the same time.

Here's an example from Metafilter 2011/11/06:
(times saved in the server-Pacific Time- compared to times shown on my computer-Eastern time)

PT ____ ET
22:59 - 1:59
23:01 - 1:01 -here EST kicks in
23:56 - 1:56
00:04 - 2:04
00:58 - 2:58
01:02 - 4:02 - this is just weird
01:54 - 4:54
01:01 - 4:01 -here PST kicks in

Notice that in Eastern time, there a two 1AM-1:59AMs, two 4AM-4:59AMs, and no 3:xx times.posted by MtDewd at 11:27 AM on November 5, 2012

if you change the timestamp format in one table you have to change it everywhere, mixed formats are bad mojo. How many tables?

Then you have to find every instance of code where you use any of those timestamps in any of those programs. How many programs?

Unless you know the answers to those two questions you can't estimate how difficult it would be. pb obviously does I'll take his word on it.
posted by Bonzai at 2:34 PM on November 5, 2012 [1 favorite]