An Hour in Time

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

  • Andrew Beattie-470886

    Newbie

    Points: 1

    Please will you try and understand that the USA is not the whole world, or even the main part of it. SQLServerCentral is available world wide. In Europe clocks change on 28 October. I do not know when they change in India or China. But some recognition of the world at large would be nice

  • Sarah Dutkiewicz

    SSCommitted

    Points: 1760

    Andrew Beattie (10/17/2007)


    Please will you try and understand that the USA is not the whole world, or even the main part of it. SQLServerCentral is available world wide. In Europe clocks change on 28 October. I do not know when they change in India or China. But some recognition of the world at large would be nice

    Not all of the US changes either. The US has issues with time changes this year because laws made changes that got most of the states on Daylight Savings Time this year. Add to it that they changed the rules and now are setting standard rules for when it starts and stops. So although other countries have DST and clock changes, the US change is different because it's not the same like every year and his article reminds those who are affected to just keep the change in mind. On top of it, Steve is based here in the States, so it makes sense to see an editorial on something related to DBAs and what's going on at home, even if it is a worldly audience.

  • Gift Peddie

    SSC Guru

    Points: 73570

    All of US now changes because Indiana was blackmailed to join the change or loose 4 billion plus in federal funds. When I was in Indiana I had to use eastern for my computer regional because Indiana give wrong time.

    Kind regards,
    Gift Peddie

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

    Point taken and I should have noted the time change dates in other places.

    Please also remember that I live in the US and we have roughly 80% of the audience from the US, so we do tend to lean towards the happenings there.

  • jay-h

    SSCoach

    Points: 18808

    On the tech side...

    what happens if you try to restore to a point in time 01:30 on Nov 4?

    ...

    -- FORTRAN manual for Xerox Computers --

  • Timothy-313907

    Ten Centuries

    Points: 1226

    This is one reason I'm liking the idea of storing dates in UTC rather than local time. It would be a bit of a hassle to display the dates later on though because you'd have to convert the date to the user's local time, instead of just displaying it. Still, you'd be immune to daylight savings time and time zone issues.

  • Timothy-313907

    Ten Centuries

    Points: 1226

    Speaking of time zones, how do you adjust the time zone settings on this forum? I made my last post at 10:29am EDT but it displays at 3:29pm, I'm assuming in UTC + 1. Ha, now that I think about it this issue is a perfect example of UTC / local time craziness. 😛

    Never mind, found it.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

    You can adjust the timezone in your forum profile (click control panel above).

    For the restore, when's the log from? I'd think that you've have log records that looked like:

    1 - 1:45am

    2 - 1:55am

    {time change}

    3 - 1:05am

    4 - 1:15am

    5 - 1:45am

    So a restore to a point in time would actually stop at transaction 1, not transaction 5. I'll check with Mr. Randal. He has a great post on what happens with SQL Agent, but I never thought about this.

    Of course, if you have log backups at 1:59, you'd only get the first backup at 1:59. According to Paul's post, SQL Agent freezes from 2-3 (old time) or the second 1-2 (new time), so you won't have backup jobs then. You could manually run new jobs, but you'd then have separate files to restore to the point in time to.

  • Sarah Dutkiewicz

    SSCommitted

    Points: 1760

    Gift Peddie (10/17/2007)


    All of US now changes because Indiana was blackmailed to join the change or loose 4 billion plus in federal funds. When I was in Indiana I had to use eastern for my computer regional because Indiana give wrong time.

    Sorry, Gift. Arizona and Hawaii are still rebelling against the time changes 😉

  • Matt Miller (4)

    SSC Guru

    Points: 124185

    That's one of those times where MS ought to consider "stealing" a concept from Novell (it's not like they haven't done it before ;)). NetWare 4.1 had a concept called "synthetic time", which ensured that their logs ALWAYS had "time sequential entries". So - if there was ever an occasion to have to set the clock back (be it for DST, or for any other clock synchronization screwups), it would continue to increment the clock, but it would "slow" the server time down (so the server's "synthetic time" would increment more slowly, like - at 33% of "real time") until "real time" caught up to "synthetic time".

    It made actual time tracking during the conversion a little confusing (it would log BOTH time entries so you had to be sure which one you were looking at), but it did provide a very clear mechanism to authoritatively trace through multiple logs. It would also work rather well for the described restore.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Kevin Farlee

    Old Hand

    Points: 304

    Steve Jones - Editor (10/17/2007)


    Of course, if you have log backups at 1:59, you'd only get the first backup at 1:59. According to Paul's post, SQL Agent freezes from 2-3 (old time) or the second 1-2 (new time), so you won't have backup jobs then. You could manually run new jobs, but you'd then have separate files to restore to the point in time to.

    Paul dropped me a line - Steve has it right. Logs are timestamped with localtime, with no timezone info. Restore would stop at the first record which exceeded the STOPAT value.


    Kevin Farlee
    SQL Server Storage Engine PM

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

    Hey, I was right!:w00t:

  • Kevin Farlee

    Old Hand

    Points: 304

    Note that if you planned in advance you could use named log marks to uniquely restore to any named mark in that range, regardless of time. Carrying it to the extreme, create a log mark every X seconds, named for the current UTC...


    Kevin Farlee
    SQL Server Storage Engine PM

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply