Working with Dates and Times

1. Working with Dates and Times Why does this have to be so difficult? Paul Cripwell NAV CANADA

2. Overview • Units • Display • Sample problems and solutions • Suggested standards Paul Cripwell, NAV CANADA

3. Units • 12 months in a year • 30/31/28(29) days in a month • 24 hours in a day • 60 minutes in an hour • 60 seconds in a minute Paul Cripwell, NAV CANADA

4. Leap Year • What a concept! • What a problem! Paul Cripwell, NAV CANADA

5. Display • YYYY/MM/DD:HH:MM:SS • Canadian Standard • DDMMM(YY)YY:HH:MM:SS • American Standard Paul Cripwell, NAV CANADA

6. Problem 1 • Adding and Subtracting • Crossing the day boundary, month and year • INTNX function Paul Cripwell, NAV CANADA

7. Sample 1 • INTNX(‘Minutes’, datetimefield, -60) • Subtracts 60 minutes from the field crossing any boundaries as necessary Paul Cripwell, NAV CANADA

8. Sample 2 • INTNX(‘Seconds’, datetimefield, +125) • Adds 125 seconds to the field crossing any boundaries as necessary Paul Cripwell, NAV CANADA

9. Sample 2 • INTNX(‘Day’, datefield, ‘+4’) • Adds 4 days to the field crossing any boundaries as necessary Paul Cripwell, NAV CANADA

10. Sample 3 • INTNX(‘Month’, datefield, ‘-2’) • Subtracts 2 months from the field crossing any boundaries as necessary • Date becomes the first of the month Paul Cripwell, NAV CANADA

11. DEMO Paul Cripwell, NAV CANADA

12. Problem 2 • Counting events that span long time periods into specific periods Paul Cripwell, NAV CANADA

13. Counting Events • How many flights were in an airspace during a 15 minute period? • Flights enter during the period • Flights exit during the period • Flights are in the airspace for the entire period • All need to be counted Paul Cripwell, NAV CANADA

14. Counting Events – Code 1 /* if a flight was in the air over a day boundary, then split the observation into two. First stops at midnight, second starts at midnight next day and goes until flight exits */ data local.min15prep2; set local.min15prep1; dtin=datepart(Entry_Date); dtot=datepart(Exit_Date); tmin=timepart(Entry_Date); tmot=timepart(Exit_Date); if dtin=dtot then output; else do; if dtin <> dtot and tmot=0 then output; else do; dtot=dtin; tmsv=tmot; tmot=86400; split=1; output; dtin=dtin+1; dtot=dtot+1; tmin=1; tmot=tmsv; output; end; end; format dt: mmddyy10. tm: time8. time: mdyampm24.; drop tmsv; run; Paul Cripwell, NAV CANADA

15. Counting Events – Code 2a /* Now to slice and count */ data local.min15prep4; set local.min15prep3; by Flight_rule_code Airspace_name dtin; /* create counter array and set elements to 0 when the day changes */ array slice(96); if first.dtin then do; do i = 1 to 96; slice(i)=0; end; end; /* find beginning slice and ending slice */ if tmin=0 then tmin=1; if tmot=0 then tmot=1; slin=ceil(tmin/900); slot=ceil(tmot/900); /* loop through the slices and add one to every slice between the in and out times */ do i = slin to slot; slice(i)+1; end; Paul Cripwell, NAV CANADA

16. Counting Events – Code 2b /* output on last obs of the day*/ if last.dtin then output; retain slice:; drop tail slin slot dtot i tmin tmot timein timeot flight_id FIR flight_airspace_id Entry_date Exit_date Distance_NM specialty_id fir_id split sector_id; rename dtin=date; run; /* behold ... */ Paul Cripwell, NAV CANADA

17. Suggested Standards • Use three fields to express and work with dates and time • DateTime field with all elements • Used for calculations • Datepart(DateTime) used to display date formats • Timepart(DateTime) used to display time formats Paul Cripwell, NAV CANADA

18. ? Paul Cripwell Manager, Traffic Analysis and Forecasting Finance Department NAV CANADA Paul.cripwell@navcanada.ca Paul Cripwell, NAV CANADA