5 Dates and times

Working with financial data requires careful handling of dates and times. Market data comes with timestamps that must be parsed, synchronised across time zones, and stored consistently. Poor date handling can lead to misaligned data, incorrect calculations, and analysis errors.

Financial markets operate across different time zones with varying holiday schedules, making date management more complex than in many other domains. Understanding date formats, time zones, and software representations is important for reliable financial analysis.

5.1 Data and libraries

library(lubridate)

5.2 Common date formats

A date can be represented numerically in many different ways. Consider the date 13 September 2018:

Format	Example
DD-MM-YYYY	13-09-2018
MM-DD-YYYY	09-13-2018
YYYY-MM-DD	2018-09-13
YYYYMMDD	20180913

The best way is to use the YYYYMMDD convention for two reasons:

It can be represented as an integer, not as a string, making data handling more convenient;
It sorts naturally (in chronological order).

For international data, the ISO 8601 format provides a standardised approach: YYYY-MM-DDTHH:MM:SSZ, where T separates date and time, and Z indicates UTC time. For example, 2025-02-10T14:30:00Z represents 10 February 2025, at 14:30 UTC.

5.3 Time zones and UTC

UTC (Coordinated Universal Time) serves as the primary time standard globally. Financial data is often timestamped in UTC to avoid confusion across different market hours and locations.

Major financial markets operate in different time zones: New York (EST/EDT), London (GMT/BST), Tokyo (JST), and Hong Kong (HKT). Market opening and closing times must be converted to a common reference, typically UTC, for analysis across multiple markets.

Summer time changes complicate time zone handling. The US and Europe change clocks on different dates, creating periods where the time difference between New York and London varies. For example, there are brief periods when New York is 4 hours behind London instead of the usual 5 hours.

When analysing international portfolios, all timestamps should be converted to UTC before calculations. This ensures that events are properly sequenced and that correlations between markets are not distorted by time zone misalignments. Many data providers offer timestamps in both local market time and UTC.

UTC and GMT (Greenwich Mean Time) are often used interchangeably, though technically UTC includes leap seconds to stay aligned with Earth’s rotation, which GMT does not. For practical financial analysis, this distinction rarely matters.

R’s lubridate package handles time zone conversions using the with_tz() function, though care must be taken with historical dates around summer time changes.

5.4 How software handles dates

Dates are very complicated to work with in software, as most code does not internally use years, months, etc. Instead, dates are floating point numbers relative to some initial date.

The Unix epoch (1 January 1970, 00:00:00 UTC) serves as the starting point for timekeeping in Unix-like systems, like Mac and Linux. Time is measured as the number of seconds elapsed since this moment, known as Unix time. For example, Unix timestamp 1740301494 corresponds to 2025-02-23T09:04:54Z.

Historical dates before 1970 use negative numbers. For instance, 1 July 1850 at noon UTC is -3771144000.

The Windows epoch starts on 1 January 1601, 00:00:00 UTC, which can cause compatibility issues when transferring data between systems. In addition, it cannot handle dates before that.

5.5 Software compatibility issues

Dates often present problems when using multiple languages or programs to carry out numerical work. Excel has two different conventions for dates depending on the version — the origin year can either be 1900 or 1904 — this requires verification before use. Furthermore, Excel does not allow dates before 1900.

5.6 R `lubridate` for financial data

Financial data often arrives with inconsistent date formats from different sources. The R package lubridate provides useful tools for handling these variations and converting between formats commonly used in finance.

The main date parsing functions handle different ordering conventions: - ymd(): Year-month-day format (2024-02-15) - ISO standard, common in databases - mdy(): Month-day-year format (02/15/2024) - US convention, often in US data feeds - dmy(): Day-month-year format (15/02/2024) - European convention, UK data sources

Other key functions for financial analysis: - ymd_hms(): Handle timestamps from trading systems with time components - as_datetime(): Convert Unix timestamps from APIs - with_tz(): Standardise market data to UTC - floor_date(), ceiling_date(): Aggregate intraday data to daily/weekly periods

date1 = "21-02-2024"
date2 = "02/21/2024"
date3 = "2024-02-21 14:30:00"
parsed_date1 = dmy(date1)
parsed_date2 = mdy(date2)
parsed_date3 = ymd_hms(date3)
print(parsed_date1)

[1] "2024-02-21"

print(parsed_date2)

[1] "2024-02-21"

print(parsed_date3)

[1] "2024-02-21 14:30:00 UTC"

unix_time = 1708525800
date_time = as_datetime(unix_time, tz = "UTC")
print(date_time)

[1] "2024-02-21 14:30:00 UTC"

my_date = ymd_hms("2024-02-21 14:30:00", tz = "UTC")
print(my_date)

[1] "2024-02-21 14:30:00 UTC"

unix_timestamp = as.numeric(my_date)
print(unix_timestamp)

[1] 1708525800

5.7 Common date and time issues in financial analysis

Time zone changes and anomalies can significantly affect financial data analysis. When Samoa skipped 29 December 2011, moving directly to 31 December, any financial analysis spanning this period required special handling to avoid gaps in time series data.

Similarly, when Venezuela advanced its clocks by 30 minutes in 2016 with little notice, or when Russia made last-minute time zone changes in 2014, financial systems had to quickly adjust to maintain accurate timestamps. Morocco’s practice of suspending daylight saving time during Ramadan, with dates announced close to the event, creates ongoing challenges for automated trading systems.

Unusual time zones add complexity to global analysis. Nepal operates on UTC+5:45, exactly 5 hours and 45 minutes ahead of UTC, while North Korea introduced Pyongyang Time (UTC+8:30) in 2015 before reverting to UTC+9 in 2018. These non-standard offsets require special handling in financial systems designed around hour-based time zones.

These examples highlight why financial institutions typically standardise on UTC for internal systems. Market holidays create additional gaps in data series that must be handled appropriately in analysis. Different markets observe different holidays, meaning that correlations calculated during holiday periods may not reflect normal trading relationships.

Daylight saving time transitions can create apparent jumps or gaps in intraday data series. When clocks “spring forward,” an hour disappears, while “falling back” creates a repeated hour that can lead to duplicate timestamps if not handled carefully.

Data vendors may provide timestamps in different formats or time zones, requiring standardisation before analysis. Inconsistent handling can lead to misaligned data across different securities or markets.