SPSS datetime values lack timezone metadata: how to handle regional conversions?

Converting Timezone-Aware Timestamps to SPSS

Because SPSS stores datetime values as a numeric count of seconds since October 14, 1582, without metadata, it cannot natively track timezones or automatically adjust for Daylight Saving Time (DST). To preserve the original UTC instant and avoid ambiguous local-time representations, the recommended approach is to normalize all data to UTC upon import and apply regional offsets only during final reporting.

Applying UTC Offsets and DST Logic

Since there is no SET TIMEZONE command, you must use manual arithmetic. To shift a datetime variable by a specific number of hours, use the COMPUTE command. Note that 1 hour equals 3,600 seconds.

* Example: Convert UTC to EST (UTC-5).
COMPUTE LocalTime = UTC_Timestamp - (5 * 3600).
EXECUTE.

To account for DST, you must implement conditional logic based on the specific transition dates of the target region. For example, if a region shifts to UTC-4 during the summer:

* Example: Apply DST offset for a specific date range.
IF (UTC_Timestamp >= DATE.DMY(10, 31, 2023) AND UTC_Timestamp < DATE.DMY(3, 10, 2024))
    COMPUTE LocalTime = UTC_Timestamp - (4 * 3600).
ELSE
    COMPUTE LocalTime = UTC_Timestamp - (5 * 3600).
EXECUTE.

Handling Database Imports (TIMESTAMPTZ)

The SPSSINC TRANS extension is designed for general data transformation and does not include a built-in timezone database for DST resolution. When importing TIMESTAMPTZ values from a database like PostgreSQL, the driver typically converts the value to the local time of the machine running the import. To avoid this, cast the database column to UTC explicitly in your SQL query before importing it into SPSS.

Detecting DST Gaps and Overlaps

To identify duplicate or missing hours caused by DST transitions, sort your dataset by the datetime variable and calculate the difference between consecutive records:

1. Sort cases by the datetime variable.
2. Compute the difference: COMPUTE Diff = DateTime_Var - LAG(DateTime_Var).
3. Flag anomalies: A jump of 7,200 seconds (2 hours) where 3,600 is expected indicates a DST gap; a 0 or negative difference in a strictly increasing series indicates an overlap.

Assumptions and Verification

This method assumes you are using a version of SPSS that supports standard COMPUTE and DATE functions. To verify the conversion, create a test case with a timestamp exactly one hour before and after a known DST transition and verify the output against a trusted UTC converter.

Diagnostic Detail Needed: Are you importing data via ODBC/JDBC or via a flat file (CSV)? If using ODBC, the driver's local timezone settings may be altering the data before it reaches SPSS.