| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2010-02-09 : 17:37:40
|
| I am getting data from an application that stores its dates in an integer field, in seconds from 1/1/1970...As all my work is base on US Central Standard Time, I have been using: dateadd(s,Actual_End_Date,'19691231 18:00') AS Actual_End_Dateto do date conversions from the integer to a timedate field.I now would like to consider the one hour difference based on daylight saving time...?... I was considering building a calendar table with every day and its offset (6{CST} or 5{CDT}).... but am thinking a search of every date on a table that contains every day from 1/1/2000 to 12/31/2015 (estimate a 5 year life to this work); might be a bit processing intensive...?.recommendation for a better way? please?thank you for the help! |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-09 : 18:21:16
|
| I'm not clear on what problem you are trying to see addressed but would the 'SYSDATETIMEOFFSET()' function be of any use here?=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-10 : 01:55:36
|
| SYSDATETIMEOFFSET() = SQL2008 only??I'm curious if you are storing seconds since 1-jan-1970 why you have to add them to '19691231 18:00' to get a datetime ...... and if it was me I would just store them in a DATETIME datatype in the first place (even if they came from Unix or somesuch as seconds-since-epoc.At what point do you need the time adjusted for daylight savings - Display? A calculation? Something else? |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-02-10 : 10:15:48
|
| I am in sql 2005...so the sysdateoffset is not an option...besides, I am not determining a date in the current system, I am reading an EXISTING date (represented by the integer number of seconds from 1/1/1970)...so I am reading IN from the applications history of helpdesk tickets...the application is a helpdesk application from bmc called Remedy... it stores its dates as integers (I expect with the very large number of help desk calls, and the number of dates associated with the call, they consider an integer to be a considerable space saver over a datetime field...in any event, it is ...what it is...eg. I am trying to report on questions such as "How many tickets did we close, by month, since 2007?"... initially, I did not realize the dates were stored GMT, so I was off 6 hours on the close dates of the tickets..then I realized the daylight savings time impact...granted, that only really affects tickets closed between 1-2am in the period of daylight saving time...but it does skew the numbers from the canned summary reports from the vendor's.so...given a field such as Arrival_Time that contains an integer...1184014663, I use DATEADD(s, Arrival_Time, '1969-12-31 18:00') AS Conv_arrival_time which gives me a datetime of 7/9/2007 2:57:43 PM (CST, as I subtract 6 hours, the time is store GMT)...so now I need to consider the daylight savings time scheduled since 2007... which leads me back to my current solution of a calendar table from 1/1/2007 with the date, offset (usually 6 for GMT to CST, but only 5 if I am in the CDT dates)... |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-02-10 : 10:21:10
|
| kristen...I do the conversion in SSIS when I pull the data out of the application database and populate a datamart...I am dumping the OLTP (the operational application database, into a datamart, and front-ending the sql datamart with excel pivot tables (using excel services in sharepoint), which I post into a sharepoint site for functional managers to play 'what-if'..using the pivot table filters... kinna a self-service ad-hoc reporting tool using excel as the front-end...all our managers are excel literate..this reduces significantly (about 3 FTE's) of effort in the analytics group who were constantly being asked to write new report for different slice and dice versions of the data...works kinna slick, actually... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-10 : 11:56:23
|
| You only need the start / end date/time of daylight saving time each year, rather than a calendar of all dates, don't you?You can store that as INTs, rather than datetime, to be able to directly correspond it to the INTs you are storing.yes datetime would take more space to store, but the CPU cost of convert/CAST to datetime (if that happens often) is significant too. |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-02-10 : 13:08:40
|
| mmm...I just realized that for my purposes...it doesnt matter...because daylight savings time occurs at 2am, whether the time goes from 2 to 1am...or 2 to 3am...it is still the same DAY...therefore, because I am only concerned with DAY boundaries, daylight savings time does not matter in my case...but for completeness...IF I was interested in keeping the correct TIME for those dates that did fall in the daylight saving time period, then I would do something like this:..so I create a table containg the period that daylight saving time occurs...eg. 04/02/2006 02:00:00 thru 10/29/2006 02:00:00; if my date is within this period, my offset is only 5 instead of 6 hours from GMT.... I notice that the period are all different every year...so what would be the best way to code the date conversion?My first thought was a 10 part case statement...but that seems way to clunky...mm?..Year Begin End Offset2006 2-Apr 29-Oct 52007 11-Mar 4-Nov 52008 9-Mar 2-Nov 52009 8-Mar 1-Nov 52010 14-Mar 7-Nov 52011 13-Mar 6-Nov 52012 11-Mar 4-Nov 52013 10-Mar 3-Nov 52014 9-Mar 2-Nov 52015 8-Mar 1-Nov 5 2->3 2->1 |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-02-10 : 13:16:37
|
| grrrr...on second thought...that logic falls apart... ALL times within that period will be adjusted one hour...so when a help desk call was logged at 23:01 on april 2nd of 2006, because of daylight saving time the time REALLY was at 00:01 on april 3rd...*sigh*...back to your recommendation then...how would I check each date against the new daylight saving time table, to determine if a date in in a daylight saving time period...?.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 06:12:30
|
I think you want the Day / offset table - lets call that table DSToffsets.Start Date and the amount to add to get your local time - lets say 5 or 6 hours.You store the "Start Date" as an INT (seconds since 1970), and 5 or 6 in the Offset columnFor a given helpdesk record find the nearest-earlier "Start Date" in DSToffsets, and add the OffsetIt might be easier (i.e. more efficient in the JOIN) to have DSToffsets store both the Start and End date/time values for the period (i.e. the Start Date in next row in the table must be exactly +1 - so that there are no gaps).So:SELECT Col1, Col2, ... H.CallTime + D.Offset AS LocalTimeFROM HelpDeskCalls AS H JOIN DSToffsets AS D ON D.StartDate <= H.CallTime AND D.EndDate >= H.CallTime |
|
|
|
lbrigham
Starting Member
2 Posts |
Posted - 2010-03-02 : 16:42:11
|
I have exact same problem in that a script needs devised that will account for DST in multiple time zones as this script will be run on DBs in all continental US time zones.DECLARE @UnixDateTime intSET @UnixDateTime = 1268638200SELECT DATEADD(s,@UnixDateTime+DateDiff(ss, GetUTCDate(), GetDate()),'01/01/1970') The above would accommodate all different time zones, but pukes on DST. The above should output as Mar 15, 2010 2:30am, but instead outputs as Mar 15, 2010 1:30am due to not taking into account DST (since Eastern time zone, where I currently reside and am coding this effectively becomes GMT -4 during DST).I'm trying to solve this problem without building a daylight savings time table. I was thinking logic using a combination of datepart(dw,) and datepart(d,) to get day of week (sun=1,mon=2,etc) and day of month could address this, but not exactly sure how. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-03 : 02:19:17
|
| Doesn't answer your problem, but you should not use '01/01/1970' style for date constants, the correct format is '19700101' or you should use an explicit cast. SQL's lax tolerance of string-date formats is an annoyance as it just masks the potential for you to have bugs that only appear later. |
|
|
|
lbrigham
Starting Member
2 Posts |
Posted - 2010-03-03 : 09:10:48
|
| Didn't know... thanks Kris |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-03 : 09:37:16
|
| Team!..thank you for the suggestions...my solution is to live with a one hour issue during DST...I considered:All my records are midwest..in the Central time zone...My application is a help desk application, where 99.9% of the requests are during a 12 hour period from 7am-7pm...I also aggregate to a monthly period, which makes the issue even less sensitive...So, the impact is those tickets that are on the 11pm-12am time frame, that should really be credited to the next day...Therefore, my solution is to just adjust the GMT to CDT, and live with the issue of tickets that fall on the end of month at 11pm-12am... |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-03 : 09:37:16
|
| Team!..thank you for the suggestions...my solution is to live with a one hour issue during DST...I considered:All my records are midwest..in the Central time zone...My application is a help desk application, where 99.9% of the requests are during a 12 hour period from 7am-7pm...I also aggregate to a monthly period, which makes the issue even less sensitive...So, the impact is those tickets that are on the 11pm-12am time frame, that should really be credited to the next day...Therefore, my solution is to just adjust the GMT to CDT, and live with the issue of tickets that fall on the end of month at 11pm-12am... |
|
|
|
|
date conversion, daylight saving time