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

• For maximum compatibility with other software/languages, avoid using the datetime format directly.
• When you want to represent datetime and its time zone, first convert the datetime to UTC. Then store the date and time separately, e.g., date_utc and time_utc.
• This is how WRDS handles time zones 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 the local time zone as default (“MST” in the following case)

# 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 a time zone specified, the time zone is explicitly used/displayed.

# create a POSIXct with timezone specified
# 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 converts a POSIXct to hms.

# 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 store a UTC datetime as separate date and time columns. To combine them, use the following snippet:

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

Results:

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

You may be tempted to use the following code:

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 by 86,400 does not always equal 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 involve unwanted time zones.
• The solution is simple: during conversion, force datetime64[ns] to be a date and then save, e.g.,

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

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

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