Date and Time Values

Date and time values in the data files are recognized based on the format of each variable. Many data/time formats can be recognized without user intervention.^[1] In case certain date/time formats are not recognized, they can be added easily.

The Three Categories

The variable formats defined by the statistical software are classified into three categories that affect how they are stored in memory and translated to Julia types: datetime, date and time. Within a category, there can be multiple formats but they only alter how data are displayed on screen (in the corresponding software) without affecting how they are stored in memory.

Category	Explanation
datetime	Record both the date and the time within a day in a single value
date	Record only the date in a value
time	Record either time within a day or length of period without calendar date

The statistical software represents variables of these formats as numeric values internally based on certain rules. Stata provides formats dedicated to datetime (%tc) and date (e.g., %td) but not time. For a pure time variable without meaningful date, %tc may be used if time strictly refers to a time point within a date. SAS and SPSS have built-in formats of all three categories. For SAS, variables of the three categories are recorded as numeric values in three different ways. SPSS uses the same approach to represent both datetime and date but only alters how they are displayed. For (pure) time variables, SAS and SPSS record them as the number of seconds elapsed (without a specific starting point on calendar).

Translating Datetime and Date

All datetime or date formats from Stata, SAS and SPSS are stored as the numbers of periods elapsed since a reference datetime or date (epoch) chosen by the software. Therefore, knowing the epoch and the length of a single period is sufficient for uncovering the represented datetime or date for a given format.

Info

Two exceptions are Stata format "%tw" for weeks and "%ty" for years. Stata always counts the week numbers starting from the first day of a year. Each year always consists of 52 weeks. Any remaining day at the end of a year is counted as the 52th week within that year. Conversion for a variable with format "%tw" is therefore handled differently. For "%ty", the recorded numeric values are simply the calendar years without any transformation. A variable with format "%ty" is not converted to Julia Date or DateTime.

If a variable is in a recognized format, the values will be displayed as Julia Datetime or Date when printing a ReadStatTable. Notice that the underlying numeric values are preserved and the conversion to the Julia Date or DateTime happens only lazily via a MappedArray when working with a ReadStatTable.

julia> using ReadStatTables, DataFrames
julia> tb = readstat("data/sample.dta")5×7 ReadStatTable:
 Row │  mychar    mynum      mydate                dtime         mylabl        ⋯
     │ String3  Float64       Date?            DateTime?  Labeled{Int8}  Label ⋯
─────┼──────────────────────────────────────────────────────────────────────────
   1 │       a      1.1  2018-05-06  2018-05-06T10:10:10           Male        ⋯
   2 │       b      1.2  1880-05-06  1880-05-06T10:10:10         Female        ⋯
   3 │       c  -1000.3  1960-01-01  1960-01-01T00:00:00           Male        ⋯
   4 │       d     -1.4  1583-01-01  1583-01-01T00:00:00         Female        ⋯
   5 │       e   1000.3     missing              missing           Male        ⋯
                                                               2 columns omitted
julia> tb.mydate5-element mappedarray(ReadStatTables.Num2DateTime{Date, Dates.Day}(Date("1960-01-01"), Dates.Day(1)), ReadStatTables.DateTime2Num{ReadStatTables.Num2DateTime{Date, Dates.Day}}(ReadStatTables.Num2DateTime{Date, Dates.Day}(Date("1960-01-01"), Dates.Day(1))), ::SentinelArrays.SentinelVector{Float64, Float64, Missing, Vector{Float64}}) with eltype Union{Missing, Date}:
 2018-05-06
 1880-05-06
 1960-01-01
 1583-01-01
 missing
julia> tb.mydate.data5-element SentinelArrays.SentinelVector{Float64, Float64, Missing, Vector{Float64}}:
   21310.0
  -29093.0
       0.0
 -137696.0
        missing
julia> colmetadata(tb, :mydate, "format")"%td"

The variable-level metadata key named format informs ReadStatTable how the numeric values should be interpreted. Changing the format may affect how the values are displayed, although the numeric values remain unchanged.

julia> colmetadata!(tb, :mydate, "format", "%tm")ColMetaIterator{ReadStatColMeta} with 7 entries:
  :mychar => ReadStatColMeta(character, %-1s)
  :mynum  => ReadStatColMeta(numeric, %16.2f)
  :mydate => ReadStatColMeta(date, %tm)
  :dtime  => ReadStatColMeta(datetime, %tc)
  :mylabl => ReadStatColMeta(labeled, %16.0f)
  :myord  => ReadStatColMeta(ordinal, %16.0f)
  :mytime => ReadStatColMeta(time, %tcHH:MM:SS)
julia> tb.mydate5-element mappedarray(ReadStatTables.Num2DateTime{Date, Dates.Month}(Date("1960-01-01"), Dates.Month(1)), ReadStatTables.DateTime2Num{ReadStatTables.Num2DateTime{Date, Dates.Month}}(ReadStatTables.Num2DateTime{Date, Dates.Month}(Date("1960-01-01"), Dates.Month(1))), ::SentinelArrays.SentinelVector{Float64, Float64, Missing, Vector{Float64}}) with eltype Union{Missing, Date}:
 3735-11-01
 -0465-08-01
 1960-01-01
 -9515-05-01
 missing
julia> colmetadata!(tb, :mydate, "format", "%8.0f")ColMetaIterator{ReadStatColMeta} with 7 entries:
  :mychar => ReadStatColMeta(character, %-1s)
  :mynum  => ReadStatColMeta(numeric, %16.2f)
  :mydate => ReadStatColMeta(date, %8.0f)
  :dtime  => ReadStatColMeta(datetime, %tc)
  :mylabl => ReadStatColMeta(labeled, %16.0f)
  :myord  => ReadStatColMeta(ordinal, %16.0f)
  :mytime => ReadStatColMeta(time, %tcHH:MM:SS)
julia> tb.mydate5-element SentinelArrays.SentinelVector{Float64, Float64, Missing, Vector{Float64}}:
   21310.0
  -29093.0
       0.0
 -137696.0
        missing

For datetime and date variables, copying a ReadStatTable (e.g., converting to a DataFrame) may drop the underlying numeric values. Hence, users who wish to directly work with the underlying numeric values may want to preserve the ReadStatTable generated from the data file.

julia> df = DataFrame(tb)5×7 DataFrame
 Row │ mychar   mynum    mydate     dtime                mylabl          myord ⋯
     │ String3  Float64  Float64?   DateTime?            Labeled{Int8?}  Label ⋯
─────┼──────────────────────────────────────────────────────────────────────────
   1 │ a            1.1    21310.0  2018-05-06T10:10:10  Male            low   ⋯
   2 │ b            1.2   -29093.0  1880-05-06T10:10:10  Female          mediu
   3 │ c        -1000.3        0.0  1960-01-01T00:00:00  Male            high
   4 │ d           -1.4  -137696.0  1583-01-01T00:00:00  Female          low
   5 │ e         1000.3  missing    missing              Male            missi ⋯
                                                               2 columns omitted
julia> df.mydate5-element Vector{Union{Missing, Float64}}:
   21310.0
  -29093.0
       0.0
 -137696.0
        missing

In the above example, df.mydate only contains the Date values and the underlying numeric values are lost when constructing the DataFrame. However, when writing columns with DateTime or Date back to files, writestat will convert them to numeric values based on the file extension.

The full lists of recognized datetime or date formats for the statistical software are stored as dictionary keys; while the associated values are tuples of reference datetime/date and period length.^[2] If a datetime/date format is not found in the dictionary, no type conversion will be attempted. Additional formats may be added by inserting key-value pairs to the relevant dictionaries.

julia> ReadStatTables.stata_dt_formatsDict{String, Tuple{Union{Date, DateTime}, Dates.Period}} with 6 entries:
  "%tc" => (DateTime("1960-01-01T00:00:00"), Millisecond(1))
  "%tw" => (Date("1960-01-01"), Week(1))
  "%td" => (Date("1960-01-01"), Day(1))
  "%tq" => (Date("1960-01-01"), Month(3))
  "%tm" => (Date("1960-01-01"), Month(1))
  "%th" => (Date("1960-01-01"), Month(6))
julia> ReadStatTables.sas_dt_formats["DATETIME"](DateTime("1960-01-01T00:00:00"), Dates.Second(1))
julia> ReadStatTables.spss_dt_formats["DATE"](DateTime("1582-10-14T00:00:00"), Dates.Second(1))

Translating Time

Time variables in SAS and SPSS do not necessarily represent a time point in a day. They are simply recorded as number of seconds elapsed, which are allowed to go above 24 hours. For this reason, it is not always possible to convert time variables to Julia Time, which strictly refers to time in a day ranging from 00:00:00 to 23:59:59.999999999. Depending on the use case, time variables in SAS and SPSS may either be time in a day just like Julia Time or time duration that is more like a Julia TimePeriod.

Without imposing a specific interpretation for time variables, when retrieving a variable with a time format, ReadStatTable wraps the column as HMSCol. This custom vector type lazily converts the number of seconds to a more readable H:MM:SS.dd format when needed to ease a quick browsing of the data. It is up to the users to decide how the time variables should be processed in Julia. The underlying numeric values representing the number of seconds are preserved and can be retrieved by calling refarray. When writing a table back to SAS/SPSS files, columns with element type being Time or HMS are saved as number of seconds. For Stata, users are expected to take an explicit stand on how such variables should be stored by converting the data to either DateTime or numeric values.

ReadStatTables.HMS — Type

HMS{T}

A wrapped numeric value of type T representing the number of seconds printed in a time format H:MM:SS.dd on REPL. The value may represent either the duration of time elapsed or a time point in a day.

H is either the number of hours elapsed or hours in a day. Unlike Dates.Time, H is allowed to be greater than 23 because it may represent duration. MM is for minutes (00 to 59). SS is for seconds (00 to 59). dd is for decimal fractions of a second, rounded to the closest 1/100 second (00 to 99).

Some basic operations are supported, including comparison and addition/subtraction. For more involved time operations, users are expected to convert an instance of HMS to a more specialized object. Conversion to Dates.Time is supported if H falls in 0 to 23. To retrieve the wrapped numeric value, call unwrap.

Examples

julia> t1 = HMS(99999.99)
27:46:39.99

julia> t2 = HMS(-123.4567)
-0:02:03.46

julia> t1 < t2
false

julia> t1 + t2
27:44:36.53

julia> t1 - t2
27:48:43.45

julia> t3 = HMS(12345.6789)
3:25:45.68

julia> Time(t3)
03:25:45.6789

julia> unwrap(t3)
12345.6789

source

ReadStatTables.HMSCol — Type

HMSCol{T, A<:AbstractVector{T}} <: AbstractVector{HMS{T}}

A column (vector) that lazily maps number of seconds to HMS for readability.

HMSCol simply wraps a data column containing the numeric values so that any element retrieved is converted to HMS on the fly. The array of values underlying an HMSCol can be accessed via refarray. Users are expected to convert the data column appropriately for further processing, as HMSCol is not intended for usage beyond a quick view of data on REPL.

source

1For Stata, all date/time formats except "%tC" and "%d" are supported. The only difference between the "%tC" format and the "%tc" format is that "%tC" takes into account leap seconds while "%tc" does not. Since the DateTime type in the Dates module does not allow leap seconds, only the "%tc" format is supported. The "%d" format that appears in earlier versions of Stata is no longer documented in recent versions. For SAS and SPSS, the coverage of date/time formats might be less comprehensive.
2For Stata, the reference for date/time value translation is the official Stata documentation. Only the first three characters in the format strings affect the coding. For SAS and SPSS, the reference is pyreadstat/_readstat_parser.pyx.