All you need to know about data/time in R and SAS

• For the most compatibility with other software/programming languages, don’t use datetime format!
• When you want to represent datetime and its time zone, first, convert the datetime to UTC. Then store the date part and time part separately, e.g., date_utc and time_utc.
• This is how WRDS handles time zone in SAS.

• When created without specifying a time zone, the local time zone is used. However, even in this case, no tz attribute is created. R implicitly treats a no-time-zone POSIXct as if it has the local time zone.
• When a POSIXct has no time zone, R will use local time zone as default (“MST” in the following case)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# create a POSIXct without specifying timezone
# you'll see local time zone is implicitly used
d1 = as.POSIXct('1970-01-01 08:00:00')
d1
#> [1] "1970-01-01 08:00:00 MST"

# but you'll see no `tz` attribute
attributes(d1)
#> $class
#>   'POSIXct''POSIXt'
#> $tzone
#>   ''

# the following case again shows that local time zone is implicitly used
# note the time is converted to 10:00:00
d1 %>% with_tz('America/New_York')
#> [1] "1970-01-01 10:00:00 EST"

• When created with time zone specified, the time zone is explicitly used/displayed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# create a POSIXct with timezone speficied
# you'll see the time zone is explicitly displayed (EST)
d1 = as.POSIXct('1970-01-01 08:00:00', tz = 'America/New_York')
d1
#> [1] "1970-01-01 08:00:00 EST"

# now you see the `tz` attribute
attributes(d1)
#> $class
#>   'POSIXct''POSIXt'
#> $tzone
#>   'America/New_York'

# the following case again shows that local time zone is implicitly used
d1 %>% with_tz('Asia/Shanghai')
#> [1] "1970-01-01 21:00:00 CST"

With the help of the hms package, we can easily handle time now.

In hms, time is stored as the number of seconds since 00:00:00.

as_hms is used to convert a POSIXct to hms.

1
2
3
4
5

# Say we want to search a vector of `POSIXct` for any observations that are after 3pm

datetime = ymd_hms('2000-01-01 17:00:00')
datetime[as_hms(datetime)<as_hms('15:00:00')]
#> TRUE

As discussed above, WRDS likes to separately store a UTC datetime into a date and a time. To combine them, use the following snippet:

1
2

# Don't use `as.POSIXct`. You must use `as_datetime`!
data[, ':='(datetime=lubridate::as_datetime(date_utc, tz='UTC') + time_utc)][]

Results:

The rational behind is that POSIXct stores data as the number of elapsed seconds. Since our time is already in double, we just need to add it.

You may be tempted to use the following code:

1

data[, ':='(datetime_gold=as.POSIXct(as.numeric(date)*86400+time, origin='1970-01-01', tz='UTC'))]

This is problematic because of leap seconds. Multiplying the number of elapsed days with 86400 not always equals to the elapsed seconds in UTC.

If the original downloaded data is in SAS format (sas7bdat):

• When we convert the downloaded sas7bdat into feather, pandas/haven will sometimes convert a date object to a datetime object, and hence involves unwanted time zone.
• The solution is simple: during the conversion, force datatime64[ns] to be a date and then save, e.g.,

1
2

# force datetime64[ns] to date
df['mostimportantdateutc'] = df['mostimportantdateutc'].dt.date

• Sometimes the original column is of datatime. In this case, we need to explicitly add a time zone:

1
2

# add a time zone
df['mostimportantdateutc'] = df['mostimportantdateutc'].dt.tz_localize('UTC')