Post reply

Sql ,Daylight saving and Stored Porcedures

  • May 13, 2007 at 10:53 pm

    #118749

    Hi

    I would like to manipulate the datetime field of data logged into SQL tables

    We use Daylight saving. The SCADA system doesn't reliably update the Clocks in each controller. Therefore I want to keep the time constant in the Controllers and change the Data stored in SQL.

    My Idea..

    1 Detect Daylight saving is active

    2 Modify all records from now on by subtracting 1 Hour from Date time Column

    3 Else do nothing

    Can this be done with a Stored proc if so How? or is there a better approach?

    Cheers

     

  • May 14, 2007 at 12:33 am

    #706118

    I'm not really sure what the SCADA system is but I suppose it's some sort of system that has disparate controllers and gets upset with local time zone changes such as what happens with daylight savings time...

    The very best way to store and return data would be to store the data in GMT/UTC + 0 time and then use a view to return the data with an appropriate offset applied. This way you don't need to do bulk updates of records.

    Perhaps you could elaborate on your scenario a bit by explaining what sort of errors would occur with the SCADA system if you didn't translate the time, etc.

    Cheers

  • May 14, 2007 at 7:53 am

    #706223

    I agree with Ian. SQL follows the OS clocks, and if they're not updating, that's an issue. If you have machines in other time zones, use a standard clock, like UTC, for your data and then have the application alter it.

    Altering data like this is bad. It fundamentally lowers the integrity of your data. What if you forget to do it? What if a process fails? What if something gets updated twice?

    It's a bad idea.

Viewing 3 posts - 1 through 2 (of 2 total)

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