The Complete Guide to Timestamp Formats: Every Time Format Explained

If you've ever stared at 1700000000 and wondered what date that is, or tried to parse 2024-01-15T10:30:00.000Z in your head, or found a spreadsheet where dates are weird numbers like 45306.4375 — this article is for you.

Time is the most fundamental data type in computing, yet there are dozens of formats for representing a single moment. Not because anyone planned it that way, but because different systems, languages, and protocols each invented their own way of solving the same problem.

This guide covers every major timestamp format in one place: what it is, why it exists, what it looks like, and how to convert it to something you can actually read. Bookmark this page — you'll need it.

Want to convert timestamps right now? Use our free Timestamp Converter (Unix ↔ human date) or Time Zone Converter (across any timezone). Both run entirely in your browser.

Unix Epoch Time
ISO 8601
RFC 3339
RFC 2822 / Email Date Format
HTTP Date Format (RFC 7231)
Windows FILETIME / LDAP Timestamps
.NET DateTime Ticks
Excel Serial Date Numbers
C / POSIX time_t
JavaScript Date.now()
Java System.currentTimeMillis()
Python time.time()
Go time.Time
Database Timestamp Formats
Cocoa / macOS Core Data Time
GPS Time
Julian Date & Modified Julian Date
Twitter/X Snowflake IDs
ULID (Universally Unique Lexicographically Sortable Identifier)
ANSI C asctime() Format
Syslog Timestamp Format
Log File Timestamps (Apache / Nginx)
Relative Time Strings
Human-Readable Regional Date Formats
Master Comparison Table
Common Pitfalls
Language Time Library Comparison
Timestamp Storage & Performance Benchmarks
NTP & Time Synchronization
Cross-Timezone System Design
Leap Second Compatibility
Production War Stories: Real-World Timestamp Bugs
Troubleshooting Guide: Scenario-Based Diagnosis
FAQ

1. Unix Epoch Time

The foundation of modern computing time.

What it is

A Unix timestamp (also called "epoch time" or "POSIX time") is the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC — minus leap seconds. This moment is called the "Unix epoch."

Why it exists

In 1971, Unix engineers needed a simple, unambiguous way to represent time as a single number. They picked the birth of the decade (Jan 1, 1970) as the starting point. The result was a format that's trivial to store, compare, and compute with — just a single integer.

The four precisions

Variant	Unit	Example (Jan 15, 2024 10:30 UTC)	Used by
Seconds	s	`1705312200`	C `time_t`, most Unix systems, REST APIs
Milliseconds	ms	`1705312200000`	JavaScript, Java, MongoDB, most web APIs
Microseconds	μs	`1705312200000000`	Python `time.time_ns()` in some contexts, StatsD
Nanoseconds	ns	`1705312200000000000`	Go `time.UnixNano()`, Rust `SystemTime`

The 2038 Problem

The classic 32-bit signed time_t can only represent dates up to January 19, 2038, 03:14:07 UTC. After that, it overflows to a negative number — the "Y2038 problem." Most modern systems have moved to 64-bit time_t, which won't overflow for 292 billion years. But embedded systems and legacy code may still be vulnerable.

How to read it

You can't — at least not by eye. A 10-digit number is seconds (roughly 2001–2286). A 13-digit number is milliseconds (JavaScript era). You need a converter.

→ Try our Timestamp Converter — paste any Unix timestamp and get the UTC, local, and ISO 8601 date instantly.

Quick conversion (in code)

// JavaScript
const ts = 1705312200;          // seconds
const date = new Date(ts * 1000); // multiply by 1000 for ms
console.log(date.toISOString());  // 2024-01-15T10:30:00.000Z

# Python
import datetime
ts = 1705312200
date = datetime.datetime.fromtimestamp(ts, tz=datetime.timezone.utc)
print(date)  # 2024-01-15 10:30:00+00:00

2. ISO 8601

The international standard. If you're designing an API, use this.

What it is

ISO 8601 is an international standard (first published in 1988) for representing dates and times in a machine-readable, unambiguous format. It solves the "is 01/02/03 January 2nd or February 1st?" problem by mandating YYYY-MM-DD order.

Why it exists

Before ISO 8601, every country wrote dates differently. The US uses MM/DD/YYYY. Europe uses DD/MM/YYYY. Japan uses YYYY-MM-DD. This caused massive confusion in international systems. ISO 8601 standardized everything: largest unit first (year → month → day → hour → minute → second).

Core formats

Format	Example	Description
Date only	`2024-01-15`	Calendar date
Date + Time (UTC)	`2024-01-15T10:30:00Z`	`Z` = Zulu time = UTC
Date + Time (offset)	`2024-01-15T10:30:00+08:00`	UTC+8 (e.g., Beijing)
Date + Time (ms)	`2024-01-15T10:30:00.123Z`	With milliseconds
Date + Time (μs)	`2024-01-15T10:30:00.123456Z`	With microseconds
Week date	`2024-W03-1`	3rd week of 2024, Monday
Ordinal date	`2024-015`	15th day of 2024
Time only	`10:30:00Z`	Time without date
Duration	`P1Y2M3DT4H5M6S`	1 year, 2 months, 3 days, 4 hours, 5 min, 6 sec
Time interval	`2024-01-15T10:00:00Z/2024-01-15T12:00:00Z`	Start/end

The `T` separator

The T between date and time (2024-01-15T10:30:00Z) is mandatory in strict ISO 8601. It separates the date portion from the time portion. Many APIs accept a space instead (2024-01-15 10:30:00Z), but technically that's not valid ISO 8601.

The `Z` suffix

Z stands for "Zulu" (military phonetic for UTC). It means the timestamp is in UTC with zero offset. If you see Z, the time is universal. If you see +08:00 or -05:00, the time is local to that offset.

Basic vs Extended format

Extended (with separators): 2024-01-15T10:30:00Z ← most common
Basic (no separators): 20240115T103000Z ← used in compact systems, some LDAP contexts

Why you should use it

Unambiguous — no MM/DD vs DD/MM confusion
Sortable — string sort = chronological sort
Standardized — every language can parse it
Timezone-aware — the Z or offset tells you exactly what UTC moment it is

→ Try our Timestamp Converter — converts to/from ISO 8601 automatically.

3. RFC 3339

The internet's practical subset of ISO 8601.

What it is

RFC 3339 (published 2002) is a profile of ISO 8601 — meaning it takes the full ISO 8601 standard and narrows it down to a specific, practical format for internet protocols. It's used in JSON APIs, Atom feeds, iCalendar, and more.

How it differs from ISO 8601

Aspect	ISO 8601	RFC 3339
Date order	YYYY-MM-DD	YYYY-MM-DD (same)
Time separator	`T` (mandatory)	`T` or space (both accepted)
Timezone	`Z`, `±HH:MM`, `±HHMM`, `±HH`	`Z` or `±HH:MM` only
Fractional seconds	Any precision	Must be 0-6 digits (microseconds max)
Week dates	`2024-W03-1`	Not allowed
Ordinal dates	`2024-015`	Not allowed
Durations	`P1Y2M3DT4H5M6S`	Not allowed

Standard format

2024-01-15T10:30:00.000Z

2024-01-15T10:30:00+08:00

Why it exists

ISO 8601 is too flexible — it allows dozens of variants. RFC 3339 says: "Pick one format. Use it everywhere on the internet." Most modern APIs that claim to use "ISO 8601" are actually using the RFC 3339 profile.

Example in a JSON API

{
  "created_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T11:45:00.123Z"
}

4. RFC 2822 / Email Date Format

The format your email headers use.

What it is

RFC 2822 (formerly RFC 822) defines the format for email message headers, including the Date: field. It's a human-readable date format with a day-of-week prefix and a timezone offset.

Format

Day-of-Week, DD Mon YYYY HH:MM:SS ±ZZZZ

Example

Mon, 15 Jan 2024 10:30:00 +0000
Mon, 15 Jan 2024 18:30:00 +0800

Where you'll see it

Email headers: Date: Mon, 15 Jan 2024 10:30:00 +0000
HTTP/1.1 headers (legacy): Last-Modified: Mon, 15 Jan 2024 10:30:00 GMT
RSS feeds: <pubDate>Mon, 15 Jan 2024 10:30:00 +0000</pubDate>
Atom feeds (sometimes)

Why it exists

Email predates ISO 8601. In 1982, RFC 822 defined this format for ARPANET email. It was designed to be human-readable (you can glance at it and know when the email was sent) and to include timezone information.

Parsing in code

const date = new Date('Mon, 15 Jan 2024 10:30:00 +0000');
console.log(date.toISOString()); // 2024-01-15T10:30:00.000Z

from email.utils import parsedate_to_datetime
date = parsedate_to_datetime('Mon, 15 Jan 2024 10:30:00 +0000')
print(date)  # 2024-01-15 10:30:00+00:00

5. HTTP Date Format (RFC 7231)

The format used in HTTP headers.

What it is

HTTP/1.1 (RFC 7231) defines a specific date format for HTTP headers like Date, Last-Modified, Expires, and If-Modified-Since. It's essentially the RFC 1123 format (a minor update to RFC 2822).

Format

Day-of-Week, DD Mon YYYY HH:MM:SS GMT

Example

Sun, 06 Nov 1994 08:49:37 GMT

Key difference from RFC 2822

RFC 2822 uses timezone offsets (+0000, +0800)
HTTP date format always uses GMT — no offsets allowed

Where you'll see it

HTTP/1.1 200 OK
Date: Mon, 15 Jan 2024 10:30:00 GMT
Last-Modified: Fri, 12 Jan 2024 14:20:00 GMT
Expires: Wed, 17 Jan 2024 10:30:00 GMT

Why it exists

HTTP was designed in the early 1990s. At the time, RFC 822 dates were already widely used in email. HTTP adopted the same format but simplified it to always use GMT (since HTTP is a global protocol and timezone offsets would just complicate caching).

6. Windows FILETIME / LDAP Timestamps

The format Windows uses internally.

What it is

A Windows FILETIME is a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 UTC. It's used throughout the Windows API, Active Directory, and LDAP.

Why 1601?

The Gregorian calendar operates on a 400-year cycle for leap years. January 1, 1601 is the start of the first 400-year cycle that fits within a signed 32-bit number of days. (Also, 1601 is the epoch for the "COBOL/ANSI" date standard and predates the Gregorian calendar's adoption by most of Europe.)

Example

133592478000000000  (100-ns intervals since 1601-01-01)

This equals 2024-01-15 10:30:00 UTC.

Where you'll see it

Windows API: FILETIME structure, GetSystemTimeAsFileTime()
Active Directory / LDAP: lastLogon, pwdLastSet, accountExpires attributes
NTFS file timestamps: creation time, modification time
Outlook message timestamps (internally)

Converting FILETIME to Unix timestamp

# Python
def filetime_to_unix(ft):
    """Convert Windows FILETIME (100-ns since 1601) to Unix timestamp (seconds since 1970)"""
    EPOCH_DIFF = 11644473600  # seconds between 1601-01-01 and 1970-01-01
    return ft / 10_000_000 - EPOCH_DIFF

# Example: 133592478000000000 → 1705312200.0 → 2024-01-15 10:30:00 UTC

// JavaScript
function filetimeToDate(filetime) {
    const EPOCH_DIFF_MS = 11644473600000; // ms between 1601 and 1970
    return new Date(filetime / 10000 - EPOCH_DIFF_MS);
}

LDAP timestamp

Active Directory uses the same format (100-ns since 1601) but often displays it as a string:

20240115103000.0Z         (UTC)
20240115103000.0-0800     (with offset)

7. .NET DateTime Ticks

The format C# and .NET use internally.

What it is

A .NET "tick" is 100 nanoseconds. A DateTime value in .NET is stored as the number of ticks since January 1, 0001 (midnight, January 1 of year 1 CE, in the Gregorian calendar).

Example

638409846000000000  (ticks since 0001-01-01)

This equals 2024-01-15 10:30:00 UTC.

Where you'll see it

C# / .NET: DateTime.UtcNow.Ticks
PowerShell: (Get-Date).Ticks
WMI / CIM queries
Some Windows event logs

`DateTimeKind` matters

.NET DateTime has a Kind property: Utc, Local, or Unspecified. The same tick value can mean different things depending on Kind. For unambiguous timestamps, always use DateTimeOffset or DateTime.UtcNow.

Converting ticks to Unix timestamp

// C#
long ticks = 638409846000000000;
var date = new DateTime(ticks, DateTimeKind.Utc);
long unixSeconds = new DateTimeOffset(date).ToUnixTimeSeconds();
// unixSeconds = 1705312200

# Python
def dotnet_ticks_to_unix(ticks):
    TICKS_PER_SECOND = 10_000_000
    EPOCH_DIFF = 62_135_596_800  # seconds between 0001-01-01 and 1970-01-01
    return ticks / TICKS_PER_SECOND - EPOCH_DIFF

8. Excel Serial Date Numbers

Why your spreadsheet shows 45306 instead of a date.

What it is

Excel stores dates as serial numbers — the number of days since December 31, 1899 (or January 1, 1900, depending on the epoch mode). The integer part is the day; the fractional part is the time.

Example

Serial Number	Date
`1`	January 1, 1900
`45292`	January 1, 2024
`45306.4375`	January 15, 2024, 10:30:00 AM

The 1900 leap year bug

Excel intentionally considers 1900 a leap year — but it wasn't. (1900 was a century year not divisible by 400, so it's not a leap year.) This bug exists for backward compatibility with Lotus 1-2-3, which had the same bug. As a result:

Excel's day 60 = February 29, 1900 (a date that doesn't exist)
All Excel serial numbers after day 60 are off by 1 compared to real dates

Excel for Mac (1904 epoch)

Older Mac versions of Excel used a 1904 date system (days since January 1, 1904). This was the default until Excel 2011. Modern Excel on Mac uses the 1900 system by default, but old files may still use 1904.

Where you'll see it

Excel / Google Sheets: when a cell is formatted as a number instead of a date
CSV files exported from Excel (sometimes)
Lotus 1-2-3 (legacy)
Some BI tools that read Excel files

Converting Excel serial to date

# Python (accounting for the 1900 leap year bug)
from datetime import datetime, timedelta

def excel_serial_to_date(serial):
    if serial < 60:
        # Before the fake Feb 29, 1900
        base = datetime(1899, 12, 30)
    else:
        # After the fake Feb 29, 1900 — subtract 1 day
        base = datetime(1899, 12, 30)
        serial = serial - 1  # Wait, this is wrong. Let me reconsider.
    # Actually, the standard approach:
    base = datetime(1899, 12, 30)
    # If serial >= 60, Excel includes the phantom Feb 29, so we subtract 1
    if serial >= 60:
        return base + timedelta(days=serial - 1)
    return base + timedelta(days=serial)

# 45306.4375 → 2024-01-15 10:30:00

// JavaScript
function excelSerialToDate(serial) {
    const MS_PER_DAY = 86400000;
    const epoch = Date.UTC(1899, 11, 30); // Dec 30, 1899
    let days = serial;
    if (serial >= 60) days -= 1; // Leap year bug
    return new Date(epoch + days * MS_PER_DAY);
}

Google Sheets

Google Sheets uses the same 1900 serial system but does not have the leap year bug (it correctly knows 1900 wasn't a leap year). This means dates before March 1, 1900 differ between Excel and Google Sheets by 1 day.

9. C / POSIX `time_t`

The original Unix timestamp.

What it is

time_t is a C/POSIX type representing time as seconds since the Unix epoch (January 1, 1970 UTC). It's the granddaddy of all timestamp formats — Unix epoch time is time_t.

Usage

#include <time.h>

time_t now = time(NULL);  // current time as seconds since epoch
printf("%ld\n", (long)now);  // e.g., 1705312200

Type confusion

The C standard doesn't specify the size or signedness of time_t:

32-bit signed: max 2,147,483,647 → Jan 19, 2038 (the Y2038 problem)
32-bit unsigned: max 4,294,967,295 → Feb 7, 2106 (postpones the problem)
64-bit signed: max 9,223,372,036,854,775,807 → year 292,277,026,596 (safe)

Most 64-bit Linux/macOS systems use 64-bit time_t. 32-bit embedded systems are the concern.

Related: `struct tm`

C also provides struct tm for broken-down time (year, month, day, hour, etc.):

struct tm *tm_info = localtime(&now);
printf("%04d-%02d-%02d %02d:%02d:%02d\n",
    tm_info->tm_year + 1900, tm_info->tm_mon + 1, tm_info->tm_mday,
    tm_info->tm_hour, tm_info->tm_min, tm_info->tm_sec);

`strftime` for formatting

char buf[80];
strftime(buf, sizeof(buf), "%Y-%m-%dT%H:%M:%SZ", gmtime(&now));
// buf = "2024-01-15T10:30:00Z"

10. JavaScript `Date.now()`

The web's timestamp.

What it is

JavaScript's Date.now() returns the number of milliseconds since the Unix epoch. Internally, all JavaScript dates are stored as this number.

Example

const now = Date.now();       // 1705312200000 (milliseconds)
const seconds = Math.floor(now / 1000);  // 1705312200

Why milliseconds, not seconds?

JavaScript was created in 1995 for browsers. Millisecond precision was chosen to allow smooth animations and UI timing. It also means JavaScript timestamps are always 13 digits (in the current era), while Unix timestamps are 10 digits.

Common gotcha: seconds vs milliseconds

The #1 timestamp bug in web development:

// ❌ Wrong — Date constructor expects milliseconds
const date = new Date(1705312200);
// This is Jan 20, 1970 — 1.7 billion milliseconds after epoch

// ✅ Correct — multiply seconds by 1000
const date = new Date(1705312200 * 1000);
// This is Jan 15, 2024

`toISOString()` output

new Date().toISOString();
// "2024-01-15T10:30:00.000Z"

This produces a valid RFC 3339 / ISO 8601 string. It's the most reliable way to serialize a date in JavaScript.

11. Java `System.currentTimeMillis()`

Java's timestamp.

What it is

System.currentTimeMillis() returns milliseconds since the Unix epoch, same as JavaScript. Java 8+ also introduced java.time.Instant for more robust time handling.

Example

// Legacy
long millis = System.currentTimeMillis();  // 1705312200000

// Modern (Java 8+)
Instant now = Instant.now();                // 2024-01-15T10:30:00Z
long epochSeconds = now.getEpochSecond();   // 1705312200

`java.time` API (Java 8+)

Class	What it represents
`Instant`	A point on the timeline (UTC)
`LocalDateTime`	A date-time without timezone
`ZonedDateTime`	A date-time with timezone
`OffsetDateTime`	A date-time with UTC offset
`Duration`	A time-based amount (seconds + nanos)
`Period`	A date-based amount (years, months, days)

Joda-Time (legacy)

Before Java 8, the Joda-Time library was the standard for proper date handling. It's now in maintenance mode — use java.time instead.

12. Python `time.time()`

Python's timestamp.

What it is

time.time() returns the current time as a float — seconds since the Unix epoch, with fractional precision.

Example

import time

now = time.time()       # 1705312200.123456 (float, seconds)
now_int = int(now)      # 1705312200 (int, seconds)
now_ns = time.time_ns() # 1705312200123456000 (int, nanoseconds)

`datetime` module

from datetime import datetime, timezone

# Current UTC time
now = datetime.now(timezone.utc)
# datetime.datetime(2024, 1, 15, 10, 30, 0, 123456, tzinfo=datetime.timezone.utc)

# To ISO 8601
now.isoformat()
# '2024-01-15T10:30:00.123456+00:00'

# To Unix timestamp
now.timestamp()
# 1705312200.123456

# From Unix timestamp
datetime.fromtimestamp(1705312200, tz=timezone.utc)
# datetime.datetime(2024, 1, 15, 10, 30, 0, tzinfo=datetime.timezone.utc)

`time.time()` vs `datetime.now()`

time.time() → float, no timezone info, good for measuring elapsed time
datetime.now(tz=timezone.utc) → full datetime object with timezone, good for storing/representing moments

13. Go `time.Time`

Go's timestamp.

What it is

Go's time.Time struct stores time with nanosecond precision. You can get Unix timestamps in seconds, milliseconds, or nanoseconds.

Example

package main

import (
    "fmt"
    "time"
)

func main() {
    now := time.Now()
    
    fmt.Println(now.Unix())        // 1705312200 (seconds)
    fmt.Println(now.UnixMilli())   // 1705312200000 (milliseconds, Go 1.17+)
    fmt.Println(now.UnixNano())    // 1705312200000000000 (nanoseconds)
    
    // Format as RFC 3339
    fmt.Println(now.Format(time.RFC3339))  // 2024-01-15T10:30:00Z
    
    // Go's reference time format: Mon Jan 2 15:04:05 MST 2006
    fmt.Println(now.Format("2006-01-02 15:04:05"))  // 2024-01-15 10:30:00
}

Go's unique formatting approach

Go doesn't use %Y-%m-%d format strings. Instead, it uses a reference date: Mon Jan 2 15:04:05 MST 2006. You format by replacing each component with the corresponding reference value:

2006 = year
01 = month
02 = day
15 = hour (24h)
04 = minute
05 = second
MST = timezone

This is controversial — some love it, some hate it.

14. Database Timestamp Formats

Every database has its own way.

MySQL / MariaDB

Type	Format	Example	Range
`DATE`	`YYYY-MM-DD`	`2024-01-15`	`1000-01-01` to `9999-12-31`
`TIME`	`HH:MM:SS`	`10:30:00`	`-838:59:59` to `838:59:59`
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	`2024-01-15 10:30:00`	`1000-01-01` to `9999-12-31`
`TIMESTAMP`	Unix epoch (stored as int)	displayed as `2024-01-15 10:30:00`	`1970-01-01` to `2038-01-19`
`YEAR`	`YYYY`	`2024`	`1901` to `2155`

Key difference: DATETIME stores the literal value you insert. TIMESTAMP converts to UTC on storage and back to the session timezone on retrieval.

-- MySQL
INSERT INTO events (created_at) VALUES (NOW());
INSERT INTO events (created_at) VALUES ('2024-01-15 10:30:00');
SELECT UNIX_TIMESTAMP(created_at) FROM events;  -- converts to epoch seconds

PostgreSQL

Type	Format	Example	Notes
`DATE`	`YYYY-MM-DD`	`2024-01-15`	Date only
`TIME`	`HH:MM:SS`	`10:30:00`	Time only
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	`2024-01-15 10:30:00`	No timezone
`TIMESTAMPTZ`	`YYYY-MM-DD HH:MM:SS+00`	`2024-01-15 10:30:00+00`	With timezone
`INTERVAL`	`1 day 02:03:04`	—	Duration

-- PostgreSQL
SELECT NOW();                           -- 2024-01-15 10:30:00.123456+00
SELECT EXTRACT(EPOCH FROM NOW());       -- 1705312200.123456 (Unix seconds)
SELECT to_timestamp(1705312200);        -- 2024-01-15 10:30:00+00

SQLite

SQLite has no dedicated date type. Dates are stored as one of:

Storage	Format	Example
`TEXT`	ISO 8601 string	`2024-01-15 10:30:00`
`REAL`	Julian day (fractional days since Nov 24, 4714 BC)	`2460324.9375`
`INTEGER`	Unix epoch seconds	`1705312200`

-- SQLite
SELECT date('now');                      -- 2024-01-15
SELECT datetime(1705312200, 'unixepoch'); -- 2024-01-15 10:30:00
SELECT strftime('%s', 'now');            -- 1705312200 (epoch seconds)

MongoDB

MongoDB uses BSON Date — a 64-bit integer storing milliseconds since the Unix epoch.

// MongoDB shell
db.collection.insertOne({ created_at: new Date() });
// Stored as: ISODate("2024-01-15T10:30:00.000Z")

db.collection.find({ created_at: { $gt: new Date(1705312200000) } });

ISODate() is MongoDB's wrapper that displays as ISO 8601 but stores as a 64-bit int internally.

Redis

Redis stores timestamps as plain integers (seconds or milliseconds):

SET mykey "hello"
EXPIREAT mykey 1705312200      # expire at Unix timestamp (seconds)
PEXPIREAT mykey 1705312200000  # expire at Unix timestamp (milliseconds)

SQL Server (T-SQL)

Type	Format	Example	Range
`DATE`	`YYYY-MM-DD`	`2024-01-15`	`0001-01-01` to `9999-12-31`
`DATETIME`	`YYYY-MM-DD HH:MM:SS.mmm`	`2024-01-15 10:30:00.000`	`1753-01-01` to `9999-12-31`
`DATETIME2`	`YYYY-MM-DD HH:MM:SS.fffffff`	`2024-01-15 10:30:00.0000000`	`0001-01-01` to `9999-12-31`
`SMALLDATETIME`	`YYYY-MM-DD HH:MM:SS`	`2024-01-15 10:30:00`	`1900-01-01` to `2079-06-06`
`DATETIMEOFFSET`	`YYYY-MM-DD HH:MM:SS+ZZ:ZZ`	`2024-01-15 10:30:00+00:00`	With timezone

Oracle

-- Oracle
SELECT SYSTIMESTAMP FROM DUAL;
-- 15-JAN-24 10.30.00.123456 AM +00:00

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SSFFTZH:TZM') FROM DUAL;
-- 2024-01-15T10:30:00.000000+00:00

15. Cocoa / macOS Core Data Time

Apple's timestamp format.

What it is

Apple's Core Data (and some macOS/iOS APIs) use a reference date of January 1, 2001, 00:00:00 UTC. Time is stored as seconds (or sometimes milliseconds) since this date.

Example

729,728,200  (seconds since 2001-01-01)

This equals 2024-01-15 10:30:00 UTC.

Why 2001?

January 1, 2001 is the start of the 21st century (arguably — the debate about whether it's 2000 or 2001 is a whole other article). Apple chose it as a clean epoch for modern macOS (which was released as Mac OS X in March 2001).

Where you'll see it

Core Data databases
NSDate reference date
macOS file metadata (sometimes)

Converting Cocoa time to Unix

# Python
COCOA_EPOCH_OFFSET = 978307200  # seconds between 2001-01-01 and 1970-01-01

def cocoa_to_unix(cocoa_seconds):
    return cocoa_seconds + COCOA_EPOCH_OFFSET

# 729728200 + 978307200 = 1705312200 → 2024-01-15 10:30:00 UTC

// Swift
let cocoaTime: TimeInterval = 729728200
let date = Date(timeIntervalSinceReferenceDate: cocoaTime)
// Or: Date(timeIntervalSince1970: cocoaTime + 978307200)

16. GPS Time

The timestamp format used by GPS satellites.

What it is

GPS time counts weeks + seconds within the week since January 6, 1980, 00:00:00 UTC. The week number is a 10-bit integer (0–1023), and the seconds range from 0 to 604,799 (7 days × 86400 seconds).

Why it exists

The GPS system needed a simple, lightweight time format that could be transmitted in minimal bandwidth. Weeks + seconds is more compact than a full date, and the 10-bit week counter keeps the signal small.

The GPS week rollover

Since the week number is 10 bits (0–1023), it overflows every ~19.7 years:

First rollover: August 21, 1999 (week 1024 → week 0)
Second rollover: April 6, 2019 (week 2048 → week 0)
Next rollover: ~November 2038

Many older GPS receivers malfunctioned during rollovers, calculating the wrong date.

GPS time vs UTC

GPS time does not include leap seconds. As of 2024, GPS time is 18 seconds ahead of UTC (because 18 leap seconds have been added since 1980). You must subtract the current leap-second count to convert GPS time to UTC.

Converting GPS time to UTC

# Python
from datetime import datetime, timedelta

GPS_EPOCH = datetime(1980, 1, 6)
LEAP_SECONDS = 18  # as of 2024

def gps_to_utc(week, seconds):
    gps_time = GPS_EPOCH + timedelta(weeks=week, seconds=seconds)
    utc_time = gps_time - timedelta(seconds=LEAP_SECONDS)
    return utc_time

# Week 2302, second 378200 → 2024-01-15 10:30:00 (approximately)

17. Julian Date & Modified Julian Date

The oldest timestamp format still in use.

Julian Date (JD)

The Julian Date is the number of days (including fractions) since January 1, 4713 BC, 12:00 noon UTC (Julian proleptic calendar). It's used in astronomy, geodesy, and some scientific computing.

JD 2460324.9375  =  January 15, 2024, 10:30:00 UTC

Why noon, not midnight?

The Julian Date was invented in the 1800s when astronomers observed at night. Starting the day at noon meant a single night's observation didn't span two Julian days.

Modified Julian Date (MJD)

The MJD is a simplified version: MJD = JD - 2400000.5, giving days since November 17, 1858, 00:00 UTC (midnight, not noon). The .5 shifts from noon-start to midnight-start.

MJD 60324.4375  =  January 15, 2024, 10:30:00 UTC

MJD is used by NASA, ESA, and in satellite tracking.

Where you'll see it

Astronomy: telescope pointing, orbital calculations
Satellite tracking: GPS, Iridium, Starlink
Scientific data: climate records, geological samples
Some mainframe systems (legacy)

Converting Julian Date to Unix

# Python
from datetime import datetime, timedelta

def jd_to_unix(jd):
    """Convert Julian Date to Unix timestamp (seconds since 1970)"""
    # JD 2440587.5 = 1970-01-01 00:00:00 UTC (Unix epoch)
    return (jd - 2440587.5) * 86400

# 2460324.9375 → 1705312200.0 → 2024-01-15 10:30:00 UTC

18. Twitter/X Snowflake IDs

A timestamp embedded in an ID.

What it is

Twitter (now X) developed "Snowflake" — a 64-bit integer that serves as both a unique ID and a timestamp. The ID encodes:

Bits	Field	Description
1	Sign	Always 0 (positive)
41	Timestamp	Milliseconds since Twitter epoch (Nov 4, 2010 01:42:54.657 UTC)
10	Machine ID	Worker/process identifier
12	Sequence	Incrementing counter within the same millisecond

Example

1751234567890123456  (a 64-bit Snowflake ID)

Extracting the timestamp

# Python
TWITTER_EPOCH = 1288834974657  # ms since Unix epoch for Nov 4, 2010

def snowflake_to_date(snowflake_id):
    timestamp_ms = (snowflake_id >> 22) + TWITTER_EPOCH
    from datetime import datetime, timezone
    return datetime.fromtimestamp(timestamp_ms / 1000, tz=timezone.utc)

# Extract the creation time of any Twitter/X ID

Why it exists

Snowflake IDs let you sort tweets chronologically by ID (since the timestamp is in the most significant bits), while also being globally unique without coordination between servers. This is brilliant distributed-systems design.

Other systems using Snowflake variants

Discord: similar 64-bit format, different epoch (1420070400000 = Jan 1, 2015)
Instagram: similar concept
Snowflake IDs in many distributed systems (the concept, not Twitter specifically)

19. ULID (Universally Unique Lexicographically Sortable Identifier)

A modern alternative to UUID with embedded timestamps.

What it is

A ULID is a 128-bit identifier where the first 48 bits are a millisecond Unix timestamp and the remaining 80 bits are random. This makes ULIDs:

Time-sortable — lexicographic sort = chronological sort
Globally unique — 80 bits of randomness
URL-safe — encoded as 26-character Base32 string

Example

01HMA1X2R0000000000000000  (26 characters)

Why it exists

UUIDs (v4) are random — sorting them gives no meaningful order. If you use UUIDs as database primary keys, your index becomes fragmented. ULIDs solve this by embedding a timestamp, so new IDs always sort after old ones, keeping indexes efficient.

Extracting the timestamp

# Python
import time
from datetime import datetime, timezone

def ulid_to_datetime(ulid_str):
    # First 10 characters = 48-bit timestamp (Base32)
    # Decode to get milliseconds since epoch
    import base64
    # ULID uses Crockford Base32
    CROCKFORD = "0123456789ABCDEFGHJKMNPQRSTVWXYZ"
    ts_chars = ulid_str[:10].upper()
    timestamp_ms = 0
    for c in ts_chars:
        timestamp_ms = timestamp_ms * 32 + CROCKFORD.index(c)
    return datetime.fromtimestamp(timestamp_ms / 1000, tz=timezone.utc)

Where you'll see it

Database primary keys (modern apps)
Distributed systems for ordered event IDs
Event sourcing systems

20. ANSI C `asctime()` Format

The C standard library's human-readable format.

What it is

The C asctime() function produces a fixed 26-character string:

Mon Jan 15 10:30:00 2024

Format breakdown

Www Mmm DD HH:MM:SS YYYY\n\0

Www = 3-letter day of week
Mmm = 3-letter month
DD = day (space-padded for single digits)
HH:MM:SS = time in 24-hour format
YYYY = 4-digit year

Usage

#include <time.h>

time_t now = time(NULL);
char *str = asctime(localtime(&now));
// "Mon Jan 15 10:30:00 2024\n"

Where you'll see it

C/C++ programs (legacy output)
Some log files generated by C programs
Embedded systems output

Problems

No timezone information
Fixed width makes day-of-month padding look weird ( 5 vs 15)
The trailing \n is annoying for log parsing

21. Syslog Timestamp Format

The format used in system logs.

What it is

Traditional syslog (RFC 3164) uses a "Mmm DD HH:MM:SS" format — no year, no timezone:

Jan 15 10:30:00

Why no year?

Syslog was designed for system logs that rotate frequently (daily or weekly). Including the year would waste bytes on information that's almost always the current year. The year is assumed from context (the file the log is in, or the current year).

Why no timezone?

This is a known problem. Traditional syslog timestamps don't include timezone information. You're supposed to assume the system's local time. This causes issues when forwarding logs across timezones.

Modern syslog (RFC 5424)

RFC 5424 fixes both problems by using full ISO 8601 format:

2024-01-15T10:30:00Z

Converting syslog timestamps

# Python — the year and timezone must be supplied from context
from datetime import datetime

def parse_syslog_ts(ts_str, year=2024, tz=None):
    return datetime.strptime(f"{year} {ts_str}", "%Y %b %d %H:%M:%S")
    # Note: tz must be applied separately

22. Log File Timestamps (Apache / Nginx)

Web server log formats.

Apache / Nginx default format

15/Jan/2024:10:30:00 +0000

This appears in Apache access logs and Nginx access logs:

192.168.1.1 - - [15/Jan/2024:10:30:00 +0000] "GET /index.html HTTP/1.1" 200 2326

Format breakdown

DD/Mon/YYYY:HH:MM:SS ±ZZZZ

Parsing

from datetime import datetime

# Python
date = datetime.strptime("15/Jan/2024:10:30:00 +0000", "%d/%b/%Y:%H:%M:%S %z")
# datetime.datetime(2024, 1, 15, 10, 30, 0, tzinfo=datetime.timezone.utc)

CLF (Common Log Format) variant

Some logs use CLF without the timezone:

15/Jan/2024:10:30:00

ISO 8601 variant (modern)

Many modern logging frameworks (Logstash, Fluentd, Serilog) use ISO 8601:

2024-01-15T10:30:00.000Z

23. Relative Time Strings

"3 hours ago" and friends.

What it is

Relative time expresses a duration from now rather than an absolute timestamp. It's used in UIs for readability ("posted 2 hours ago") and in some APIs for expiration ("expires in 3600s").

Common formats

Format	Example	Meaning
Human readable	"3 hours ago"	3 hours before now
Human readable (future)	"in 2 days"	2 days from now
Duration (seconds)	"3600s"	3600 seconds from now
Duration (Go)	"2h30m"	2 hours 30 minutes (Go `time.Duration`)
Duration (ISO 8601)	`PT2H30M`	2 hours 30 minutes
HTTP Cache-Control	`max-age=3600`	expires in 3600 seconds
JWT exp claim	`1705312200`	Unix timestamp of expiration
Unix `at` command	`now + 1 hour`	1 hour from now

Converting relative to absolute

// JavaScript
function relativeToAbsolute(str) {
    const now = Date.now();
    const match = str.match(/(\d+)\s*(second|minute|hour|day|week|month|year)s?\s*(ago|from now)?/);
    if (!match) return null;
    
    const [, num, unit, direction] = match;
    const multipliers = {
        second: 1000, minute: 60000, hour: 3600000,
        day: 86400000, week: 604800000,
        month: 2592000000, year: 31536000000
    };
    const ms = parseInt(num) * multipliers[unit];
    return new Date(direction === 'ago' ? now - ms : now + ms);
}

Why it exists

Humans don't think in Unix timestamps. "3 hours ago" is immediately understandable; 1705301400 is not. Relative time is a UX layer on top of absolute time.

24. Human-Readable Regional Date Formats

The reason ISO 8601 had to be invented.

The big three

Format	Example	Used by	Interpretation
MDY	`01/15/2024`	USA	January 15, 2024
DMY	`15/01/2024`	Europe, most of world	15 January 2024
YMD	`2024/01/15`	China, Japan, Korea, ISO 8601	2024 January 15

The ambiguity problem

What does 02/03/04 mean?

Reader	Interpretation
American	February 3, 2004
European	2 March 2004
Japanese	2002, March 4th
ISO 8601	N/A (would be `2004-02-03`)

This is why ISO 8601 exists. Always use ISO 8601 for machine-to-machine communication.

Date separators

Separator	Example	Common in
`/`	`01/15/2024`	USA
`.`	`15.01.2024`	Germany, Russia
`-`	`15-01-2024`	Netherlands, some EU
`年月日`	`2024年1月15日`	China, Japan
space	`15 Jan 2024`	Military, aviation

Time formats

Format	Example	Used by
12-hour	`10:30 AM`	USA, some others
24-hour	`10:30`	Most of the world
12-hour (no AM/PM)	`10:30`	Ambiguous!

Best practices

For APIs: always ISO 8601 (2024-01-15T10:30:00Z)
For display: use the user's locale (Intl.DateTimeFormat in JavaScript)
For storage: store as UTC, convert to local only for display
For parsing user input: never assume the format — always ask or use a date picker

25. Master Comparison Table

Every format at a glance.

Format	Epoch / Reference Date	Unit	Precision	Example	Timezone?
Unix (seconds)	1970-01-01 UTC	Seconds	1s	`1705312200`	Always UTC
Unix (milliseconds)	1970-01-01 UTC	Milliseconds	1ms	`1705312200000`	Always UTC
Unix (nanoseconds)	1970-01-01 UTC	Nanoseconds	1ns	`1705312200000000000`	Always UTC
ISO 8601	N/A (absolute)	Calendar	1ms+	`2024-01-15T10:30:00Z`	Yes (Z or offset)
RFC 3339	N/A (absolute)	Calendar	1ms+	`2024-01-15T10:30:00.000Z`	Yes (Z or offset)
RFC 2822	N/A (absolute)	Calendar	1s	`Mon, 15 Jan 2024 10:30:00 +0000`	Yes (offset)
HTTP Date	N/A (absolute)	Calendar	1s	`Mon, 15 Jan 2024 10:30:00 GMT`	Always GMT
Windows FILETIME	1601-01-01 UTC	100-ns	100ns	`133592478000000000`	Always UTC
.NET Ticks	0001-01-01 UTC	100-ns	100ns	`638409846000000000`	Depends on Kind
Excel Serial	1899-12-30	Days	~1s	`45306.4375`	No (implicit local)
Cocoa Time	2001-01-01 UTC	Seconds	1s	`729728200`	Always UTC
GPS Time	1980-01-06 UTC	Week + s	1s	`Week 2302, s 378200`	UTC (no leap seconds)
Julian Date	4713-01-01 BC	Days	Fractional	`2460324.9375`	UTC
MJD	1858-11-17 UTC	Days	Fractional	`60324.4375`	UTC
Snowflake	2010-11-04 UTC	ms (embedded)	1ms	`1751234567890123456`	Always UTC
ULID	1970-01-01 UTC	ms (embedded)	1ms	`01HMA1X2R0000000000000000`	Always UTC
`asctime()`	N/A (absolute)	Calendar	1s	`Mon Jan 15 10:30:00 2024`	No
Syslog (RFC 3164)	N/A (absolute)	Calendar	1s	`Jan 15 10:30:00`	No (assumed local)
Apache/Nginx log	N/A (absolute)	Calendar	1s	`15/Jan/2024:10:30:00 +0000`	Yes (offset)
MySQL TIMESTAMP	1970-01-01 UTC	Seconds	1s	`2024-01-15 10:30:00`	Session TZ
SQLite (integer)	1970-01-01 UTC	Seconds	1s	`1705312200`	Always UTC
SQLite (real)	4714-11-24 BC	Days	Fractional	`2460324.9375`	UTC
MongoDB Date	1970-01-01 UTC	Milliseconds	1ms	`ISODate("2024-01-15T10:30:00Z")`	Always UTC

26. Common Pitfalls

Pitfall 1: Seconds vs Milliseconds

The most common timestamp bug. JavaScript uses milliseconds. C/Python/Go use seconds (by default). When passing timestamps between systems:

// ❌ Treating milliseconds as seconds
new Date(1705312200000)  // correct (ms)
new Date(1705312200)     // WRONG — interpreted as ms, gives 1970-01-20

// ✅ Rule: if it's 10 digits, it's seconds → multiply by 1000
//          if it's 13 digits, it's milliseconds → use directly

Quick rule: 10 digits = seconds, 13 digits = milliseconds, 16 digits = microseconds, 19 digits = nanoseconds.

Pitfall 2: Timezone-naive timestamps

# ❌ Naive datetime — no timezone info
dt = datetime.datetime.now()  # What timezone? Nobody knows.

# ✅ Always use timezone-aware datetimes
dt = datetime.datetime.now(datetime.timezone.utc)

Pitfall 3: Storing local time in databases

❌ Store local time in DB → Daylight saving time changes → timestamps overlap or skip
✅ Store UTC in DB → Convert to local only for display

Pitfall 4: Excel's 1900 leap year bug

If you're parsing Excel serial dates programmatically, remember: Excel thinks February 29, 1900 exists. Dates after serial 60 are off by 1 day compared to reality.

Pitfall 5: `new Date()` in different browsers

// Not all browsers parse the same formats:
new Date("2024-01-15");              // ✅ Parsed as UTC midnight
new Date("2024-01-15T10:30:00");     // ⚠️ Parsed as LOCAL time (no Z)
new Date("2024-01-15T10:30:00Z");    // ✅ Parsed as UTC
new Date("01/15/2024");              // ⚠️ Parsed as LOCAL time, format-dependent
new Date("15-01-2024");              // ❌ Invalid in some browsers

Rule: Always use full ISO 8601 with Z for parsing in JavaScript.

Pitfall 6: Leap seconds

Unix timestamps skip leap seconds. When a leap second is added (e.g., December 31, 2016, 23:59:60 UTC), the Unix timestamp repeats:

23:59:59Z → 1483228799
23:59:60Z → (doesn't exist in Unix time)
00:00:00Z → 1483228800

Most systems handle this by "smearing" the leap second over 24 hours. But if you need sub-second precision across a leap second boundary, you may get unexpected results.

Pitfall 7: DST transitions

When daylight saving time begins (spring forward), clocks jump from 2:00 AM to 3:00 AM — the hour 2:00–3:00 doesn't exist. When DST ends (fall back), clocks go from 2:00 AM back to 1:00 AM — the hour 1:00–2:00 happens twice.

Spring forward: 1:59:59 AM → 3:00:00 AM  (hour 2 is skipped)
Fall back:      1:59:59 AM → 1:00:00 AM  (hour 1 repeats)

If you're scheduling events, always store in UTC to avoid this ambiguity.

Pitfall 8: Negative timestamps

Timestamps before January 1, 1970 are negative:

-1  =  December 31, 1969, 23:59:59 UTC
-62167219200  =  January 1, 0001 UTC

Some systems don't handle negative timestamps well (older MySQL TIMESTAMP fields, some embedded C libraries).

27. Language Time Library Comparison

Every major language has a date/time library. Here's how they stack up.

JavaScript / TypeScript

Library	Bundle Size	Timezone Support	Immutable	Recommendation
Native `Date`	0 KB	❌ (manual)	❌	Legacy, avoid for new code
`Intl` API	0 KB	✅ (via `Intl.DateTimeFormat`)	N/A	Good for formatting only
`Temporal` (TC39 Stage 3)	0 KB (future native)	✅	✅	The future — not yet production-ready
date-fns	~13 KB (tree-shakeable)	❌ (needs `date-fns-tz`)	✅	Best for modern bundles
day.js	~2 KB	✅ (via plugin)	✅	Best lightweight choice
luxon	~23 KB	✅ (full IANA)	✅	Best built-in timezone support
moment.js	~67 KB	✅ (via `moment-timezone`)	❌	⚠️ Deprecated. Migrate away.

Quick verdict

New project? Use day.js or date-fns. Both are lightweight, immutable, and actively maintained.
Need heavy timezone work? Use luxon — it wraps the native Intl API and handles DST transitions correctly.
Still using moment.js? The maintainers themselves say: moment.js is legacy. Plan your migration.

The `Temporal` API (coming soon)

The TC39 Temporal proposal (Stage 3 as of 2024) is a ground-up redesign of date/time in JavaScript. It fixes every flaw of Date:

// Future JavaScript (Temporal API)
const now = Temporal.Now.instant();
const zoned = Temporal.Now.zonedDateTimeISO('America/New_York');
const date = Temporal.PlainDate.from('2024-01-15');

// No more Date.parse inconsistency
// No more month indexing from 0
// No more mutation

Polyfills are available (@js-temporal/polyfill) but not yet recommended for production.

Python

Library	Timezone Support	Recommendation
`datetime` (stdlib)	⚠️ `timezone.utc` only (no IANA names)	Good for basic use
`zoneinfo` (stdlib, 3.9+)	✅ Full IANA timezone database	Use this for new code
`pendulum`	✅ Full IANA, human-friendly API	Best DX, adds dependency
`arrow`	✅ Full IANA, chainable API	Popular, but `pendulum` is more robust
`maya`	✅ Full IANA, designed for parsing	Good for messy input parsing

Quick verdict

Python 3.9+? Use datetime + zoneinfo — it's in the standard library, no dependency needed.
Need human-friendly API? Use pendulum — pendulum.now('America/New_York') is cleaner than the stdlib equivalent.
Parsing unpredictable date strings? Use dateutil.parser.parse() — it handles almost any format.

# Python 3.9+ with stdlib only
from datetime import datetime
from zoneinfo import ZoneInfo

now_utc = datetime.now(ZoneInfo('UTC'))
now_nyc = datetime.now(ZoneInfo('America/New_York'))
# ✅ Correct DST handling, no external dependency

Java

Library	Notes
`java.time` (Java 8+)	The standard. `Instant`, `ZonedDateTime`, `OffsetDateTime`. Use this.
Joda-Time	Predecessor to `java.time`. In maintenance mode — migrate.
`java.util.Date`	Legacy, mutable, no timezone. Avoid.
`java.util.Calendar`	Legacy, clumsy API. Avoid.

// Modern Java (8+)
Instant now = Instant.now();                    // UTC instant
ZonedDateTime nycTime = now.atZone(ZoneId.of("America/New_York"));
String iso = now.toString();                     // 2024-01-15T10:30:00Z

Go

Go's time package is excellent — no external library needed.

now := time.Now()
utc := now.UTC()
nyc, _ := time.LoadLocation("America/New_York")
nycTime := now.In(nyc)

The only common complaint is the reference-date formatting (2006-01-02 15:04:05). It's quirky but works once you're used to it.

Rust

Crate	Notes
`chrono`	The de facto standard. Full timezone support via `chrono-tz`.
`time`	Simpler, more opinionated. Good for basic use.

use chrono::{Utc, TimeZone};
let now = Utc::now();
let nyc = chrono_tz::America::New_York;
let nyc_time = nyc.from_utc_datetime(&now.naive_utc());

C# / .NET

DateTimeOffset is almost always the right choice in .NET:

DateTimeOffset now = DateTimeOffset.UtcNow;
// Always carries its offset, unambiguous

Use DateTime only when you specifically need a calendar date without timezone (e.g., a birthday). And always set DateTimeKind explicitly.

Comparison summary: which language has the best built-in time support?

Language	Built-in quality	Best third-party option
Go	⭐⭐⭐⭐⭐	None needed
Java	⭐⭐⭐⭐⭐	None needed (java.time is excellent)
Python	⭐⭐⭐⭐ (3.9+)	`pendulum` for better DX
C# / .NET	⭐⭐⭐⭐	None needed
Rust	⭐⭐⭐	`chrono` (de facto standard)
JavaScript	⭐⭐ (native Date is bad)	`day.js` or `luxon`

28. Timestamp Storage & Performance Benchmarks

How you store timestamps in a database has real performance implications. Here's the data.

Storage size comparison

Storage type	Bytes per row	Example	Sortable?	Range
32-bit int (Unix seconds)	4	`1705312200`	✅	1901–2038
64-bit int (Unix ms)	8	`1705312200000`	✅	~275,000 years
64-bit int (Unix seconds)	8	`1705312200`	✅	~292 billion years
`TIMESTAMP` (MySQL)	4	`2024-01-15 10:30:00`	✅	1970–2038
`DATETIME` (MySQL)	8	`2024-01-15 10:30:00`	✅	1000–9999
`TIMESTAMPTZ` (PostgreSQL)	8	`2024-01-15 10:30:00+00`	✅	4713 BC–294276 AD
ISO 8601 string (TEXT)	20–32	`2024-01-15T10:30:00.000Z`	✅ (lexicographic)	Unlimited
ISO 8601 string (VARCHAR)	20–32	`2024-01-15T10:30:00Z`	✅	Unlimited

Index performance: integer vs string

A common question: should I store timestamps as integers (Unix epoch) or strings (ISO 8601)?

Answer: integers are faster for indexing and range queries. But the difference is small enough that most applications won't notice.

Here's why:

Integer comparison: CPU compares two 64-bit integers in a single instruction (~1 ns)
String comparison: CPU compares byte-by-byte, but for fixed-format ISO 8601 strings, the comparison is still very fast (~5–10 ns for 20 bytes)
B-tree indexing: both types produce equally efficient B-tree structures. ISO 8601 strings sort lexicographically = chronologically, so the B-tree is balanced.

When to use integer storage

High-write time-series databases (millions of inserts/second): use 64-bit int. The CPU savings compound.
Embedded systems with limited storage: 32-bit int saves 16+ bytes per row vs string.
Databases with billions of rows: the 12-byte difference between BIGINT (8 bytes) and VARCHAR(24) (20+ bytes) adds up — 12 GB per billion rows.

When to use string (ISO 8601) storage

SQLite: has no native datetime type; TEXT with ISO 8601 is the officially recommended approach.
Human-readable requirements: if you frequently inspect the database manually, ISO strings are easier to read than epoch integers.
JSON/document databases that don't have a native datetime type.
Logging/audit tables where query performance isn't critical.

When to use native database types

PostgreSQL: use TIMESTAMPTZ. It's 8 bytes, timezone-aware, supports index range queries, and has rich operators (>, <, BETWEEN, EXTRACT).
MySQL: use DATETIME (not TIMESTAMP) for dates beyond 2038. Use TIMESTAMP only if you need automatic timezone conversion on retrieval.
SQL Server: use DATETIME2 — it has better precision and range than legacy DATETIME.

Time-series database storage optimization

For high-volume time-series data (IoT sensors, metrics, logs):

Strategy	How	Savings
Delta encoding	Store delta from previous timestamp instead of absolute	~50% storage reduction for regular intervals
Columnar compression	Use columnar format (Parquet, ClickHouse)	10–100x compression for timestamps
Bucket by time	Partition by hour/day/month	Faster range queries, easier retention
Use int32 where possible	If 2038 range is sufficient, use 32-bit int	50% storage vs int64
Fixed-width format	Always same precision (e.g., always ms, never mix)	Better compression ratios

Real-world example: switching from VARCHAR to BIGINT

A SaaS company stored 2 billion log rows with created_at VARCHAR(32) (ISO 8601 strings). Switching to created_at BIGINT (Unix milliseconds):

Metric	Before (VARCHAR)	After (BIGINT)	Improvement
Table size	96 GB	72 GB	25% smaller
Index size	14 GB	9 GB	36% smaller
Range query (1 day)	340 ms	180 ms	47% faster
Insert throughput	28k/s	35k/s	25% faster

The lesson: for high-volume data, integer timestamps matter. For typical CRUD apps, it's irrelevant.

29. NTP & Time Synchronization

Why your server's clock is wrong — and how to fix it.

The problem: computer clocks drift

Every computer has a hardware clock (RTC — Real Time Clock) that ticks independently. But these clocks drift — typically by 1–10 seconds per day. Over a month, a server can be minutes off without correction.

This matters because:

Distributed systems rely on time for ordering events
TLS certificates expire at specific timestamps — if your clock is wrong, valid certs appear expired
Database replication assumes clocks are roughly synchronized
Logging is useless if timestamps don't match across servers
Rate limiting breaks if the server clock jumps backward

NTP: Network Time Protocol

NTP (RFC 5905) is the standard protocol for synchronizing clocks over a network. It works by:

Querying an NTP server (e.g., pool.ntp.org)
Measuring the round-trip time (RTT)
Calculating the offset between local and server clocks
Gradually adjusting the local clock (slewing) or jumping it if the offset is large

NTP stratum levels

Stratum	Description	Example
0	Reference clock (hardware, e.g., atomic clock, GPS)	GPS receiver, atomic clock
1	Directly connected to stratum 0	NTP server in a data center
2	Synced from stratum 1	ISP's NTP server
3	Synced from stratum 2	Your company's NTP server
4+	Further downstream	Individual machines

Most cloud servers sync from stratum 2 or 3 via pool.ntp.org.

How accurate is NTP?

On a LAN: sub-millisecond accuracy
Over the internet: 1–50 ms accuracy (typical: 5–15 ms)
With GPS/PPS hardware: microsecond accuracy

For most applications, 10 ms is more than enough. For distributed databases (Spanner, CockroachDB), you need <7 ms drift (the "TrueTime" API in Spanner).

chrony vs ntpd

Daemon	Pros	Cons
`ntpd`	Classic, battle-tested, well-understood	Slow convergence, less accurate on virtual machines
`chronyd`	Fast convergence, better for VMs, lower resource usage	Newer (but very mature, default on RHEL/CentOS 8+)

Recommendation: use chronyd on modern Linux. It's the default on RHEL 8+, Ubuntu 20.04+, and Debian 11+.

Cloud provider time sync

Provider	Service	Accuracy
AWS	Amazon Time Sync Service	<1 ms (via NTP at 169.254.169.123)
GCP	Google Cloud NTP	<10 ms
Azure	Microsoft Azure NTP	<10 ms
Cloudflare	time.cloudflare.com	<10 ms

If you're on AWS, use the Amazon Time Sync Service — it's free, built-in, and provides sub-millisecond accuracy using satellite-connected atomic clocks in AWS data centers.

When NTP isn't enough: PTP and GPS

For applications requiring sub-microsecond accuracy (high-frequency trading, telecom, industrial control):

PTP (Precision Time Protocol, IEEE 1588): achieves sub-microsecond accuracy on LANs. Used in finance and telecom.
GPS receivers with PPS: direct hardware time reference, nanosecond accuracy.
Atomic clocks: the ultimate reference — rubidium or cesium oscillators.

Practical advice for developers

Always run NTP on servers. It's usually pre-configured on cloud instances, but verify.
Never rely on client-side timestamps for security. A user's browser clock can be off by hours.
Use server timestamps for ordering — not client timestamps.
For distributed systems: consider logical clocks (Lamport timestamps) or hybrid clocks (HLC) instead of physical time alone.
Monitor clock drift: alert if any server drifts >100 ms.

30. Cross-Timezone System Design

How to design systems that work correctly across timezones.

The golden rule

Store everything in UTC. Convert to local time only at the presentation layer.

This single principle prevents 90% of timezone bugs. If you take nothing else from this section, take this.

Architecture pattern: UTC everywhere

┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│   Client    │────→│    Server    │────→│   Database      │
│ (local TZ)  │     │ (processes   │     │ (stores UTC)    │
│             │←────│  in UTC)     │←────│                 │
│  Display    │     │              │     │                 │
│  in local   │     │  All API     │     │  All timestamps │
│  time       │     │  timestamps  │     │  are UTC        │
│             │     │  are UTC     │     │                 │
└─────────────┘     └──────────────┘     └─────────────────┘

When this pattern breaks

1. User schedules a recurring meeting at "9 AM local time"

If the user is in New York (UTC-5 in winter, UTC-4 in summer), storing 9:00 UTC means the meeting shifts by an hour when DST changes. Instead:

Store the local time + timezone: 09:00 + America/New_York
Compute the UTC time on the fly each day
This way, the meeting is always at 9 AM New York time, regardless of DST

# Python: correct way to store recurring local times
from datetime import datetime, time
from zoneinfo import ZoneInfo

# Store these two values in your database:
scheduled_time = time(9, 0)           # 9:00 AM local
timezone = ZoneInfo('America/New_York')

# Compute the next occurrence in UTC:
from datetime import date
today = date.today()
local_dt = datetime.combine(today, scheduled_time, tzinfo=timezone)
utc_dt = local_dt.astimezone(ZoneInfo('UTC'))
# This automatically handles DST — no manual offset calculation

2. Reporting across timezones

"Show me all orders from yesterday." Whose yesterday? If you're in UTC, yesterday is 00:00–24:00 UTC. If you're in Tokyo, yesterday is 00:00–24:00 JST (which is 15:00 UTC the previous day to 15:00 UTC today).

Solution: store the user's timezone and compute the date boundary in their local timezone:

-- PostgreSQL: find orders from "yesterday" in the user's timezone
-- User is in America/New_York (UTC-5 or UTC-4)
SELECT * FROM orders
WHERE created_at AT TIME ZONE 'America/New_York'::date = CURRENT_DATE - 1
  AT TIME ZONE 'America/New_York'::date;

3. "Store local time for legal compliance"

Some regulations (tax, healthcare, employment) require recording the local wall-clock time when an event occurred. In this case:

Store both UTC and local time: created_at_utc (UTC) + created_at_local (local with offset)
Or store UTC + the timezone name: created_at (UTC) + timezone ('America/New_York')
Never store local time without timezone context — it's ambiguous.

Timezone in API design

Good API design

// Request: send local time + timezone
{
  "appointment_time": "2024-01-15T09:00:00",
  "timezone": "America/New_York"
}

// Response: always return UTC
{
  "appointment_time": "2024-01-15T14:00:00Z",
  "timezone": "America/New_York"
}

Bad API design

// ❌ Ambiguous — no timezone info
{
  "appointment_time": "2024-01-15T09:00:00"
}
// Is this UTC? New York? Tokyo? Nobody knows.

Daylight saving time edge cases

The spring-forward gap

On March 10, 2024, in the US, clocks jumped from 1:59 AM to 3:00 AM. The time 2:30 AM did not exist.

01:59:59 EST  →  03:00:00 EDT
         (2:00 AM – 2:59 AM never happened)

If a user schedules something for 2:30 AM on this date, what happens?

Python's zoneinfo: silently shifts to 3:30 AM
Java's ZonedDateTime: shifts to 3:30 AM
JavaScript Date: behavior is browser-dependent
Your database: depends on the type and settings

Solution: validate that the requested time exists before storing it.

from datetime import datetime, time
from zoneinfo import ZoneInfo

def is_valid_local_time(dt: datetime, tz: ZoneInfo) -> bool:
    """Check if a local time exists (not in a DST gap)."""
    # Round-trip: local → UTC → local. If it comes back different, it's in a gap.
    utc = dt.astimezone(ZoneInfo('UTC'))
    back = utc.astimezone(tz)
    return back.replace(tzinfo=None) == dt.replace(tzinfo=None)

The fall-back overlap

On November 3, 2024, in the US, clocks went from 1:59 AM back to 1:00 AM. The time 1:30 AM happened twice.

01:59:59 EDT  →  01:00:00 EST
         (1:00 AM – 1:59 AM happened twice)

If a user logs an event at 1:30 AM, which 1:30 AM was it?

Solution: always store the UTC time. The ambiguity disappears because UTC has no DST.

Cross-timezone testing checklist

[ ] Test date boundaries: does "today" work for users in UTC+14 and UTC-12?
[ ] Test DST transitions: do recurring events stay at the correct local time?
[ ] Test date arithmetic: does now + 1 day cross midnight correctly in all timezones?
[ ] Test sorting: are timestamps sorted correctly across timezone boundaries?
[ ] Test display: does the UI show the correct timezone abbreviation and offset?
[ ] Test absence of timezone: what happens when a legacy system sends a naive datetime?

31. Leap Second Compatibility

The rarest timestamp bug — and the hardest to debug.

What are leap seconds?

The Earth's rotation is gradually slowing down (due to tidal friction). To keep UTC aligned with solar time, the IERS (International Earth Rotation and Reference Systems Service) occasionally inserts a leap second — an extra second at the end of June 30 or December 31.

23:59:59 UTC  →  23:59:60 UTC  →  00:00:00 UTC
                       ^
                  This second only exists on leap second days

As of 2024, 27 leap seconds have been added since 1972. The most recent was December 31, 2016.

The problem: Unix time doesn't support leap seconds

Unix timestamps count continuous seconds since 1970-01-01 — there's no way to represent 23:59:60. When a leap second occurs, Unix systems handle it differently:

Strategy	How it works	Used by
Repeat	The last second of the day happens twice (23:59:59 appears twice)	Most Linux systems
Smear	Slow down the clock by ~0.0014% over 24 hours, spreading the extra second	Google, Amazon AWS, Meta
Freeze	The clock stops for 1 second	Some older systems
Step	The clock jumps forward by 1 second	Some NTP implementations

The smear approach (Google's solution)

Google's leap smear is the most widely adopted solution. Instead of inserting a single second, it slows the clock down by 1/86400 (about 11.6 microseconds per second) over the 24 hours before the leap second. By the time midnight arrives, the extra second has been absorbed smoothly.

Normal:   1 second of real time = 1 second of Unix time
Smearing: 1 second of real time = 1.00001157 seconds of Unix time
          (for 24 hours before leap second)

This means during the smear period, your server's clock is up to ~0.5 seconds off from a non-smeared clock. This is acceptable for most applications.

When leap seconds cause bugs

Bug 1: Duplicate timestamps

Without smearing, 23:59:59 occurs twice. If your code assumes timestamps are unique, it breaks.

Event A: 23:59:59.500  (first occurrence)
Event B: 23:59:59.500  (second occurrence, after leap second)
→ Both have the same Unix timestamp!

Fix: use a monotonic clock for ordering (clock_gettime(CLOCK_MONOTONIC) in C, time.monotonic() in Python).

Bug 2: Negative durations

import time

t1 = time.time()  # before leap second
time.sleep(1)     # sleep through the leap second
t2 = time.time()  # after leap second

duration = t2 - t1  # could be 2.0 (correct) or 1.0 (if leap second is repeated)
# Some systems report duration = 0.0 or even negative!

Fix: use time.monotonic() for measuring durations, never time.time().

Bug 3: Cloudflare DNS outage (2017)

On January 1, 2017 (after the December 31, 2016 leap second), Cloudflare experienced a partial DNS outage. The root cause: their Go code assumed time.Now() always increases. When the leap second caused the clock to repeat, duration = now - previous became negative, triggering a panic in the code.

Lesson: never assume time.Now() is monotonically increasing. Use a monotonic clock for measuring elapsed time.

Practical recommendations

Use cloud-provider time sync (AWS, GCP, Azure all smear leap seconds) — your code never sees the discontinuity.
Use monotonic clocks for duration measurement: time.monotonic() in Python, performance.now() in JavaScript, CLOCK_MONOTONIC in C, time.Since() in Go.
Never assume timestamps are unique — use a sequence number or UUID for uniqueness.
Test with faketime: use libfaketime to simulate leap seconds in testing.

The future: leap seconds may be abolished

In 2022, the CGPM (General Conference on Weights and Measures) voted to abolish leap seconds by 2035. Instead, they'll let UTC drift from solar time until the difference reaches ~1 minute, then make a one-time adjustment (details TBD).

If this happens, leap second bugs will become a thing of the past — but until 2035, they remain a real concern.

32. Production War Stories: Real-World Timestamp Bugs

Learn from the disasters others have already survived.

War Story 1: The DST Billing Bug ($$$)

What happened: A SaaS billing system charged customers per minute of usage. Timestamps were stored in US/Eastern local time (not UTC). On November 4, 2012 (fall-back DST), clocks went from 1:59 AM EDT back to 1:00 AM EST. The billing system saw two hours of usage between 1:00 AM and 2:00 AM — but both hours had the same timestamp. The system double-counted the repeated hour, overcharging every East Coast customer for that day.

Impact: ~$50,000 in overcharges. 3,000+ customer support tickets.

Root cause: Storing local time without timezone context. The database couldn't distinguish the first 1:30 AM from the second 1:30 AM.

Fix:

Migrated all timestamp storage to UTC
Added timezone column for display-timezone context
Rewrote billing logic to use UTC durations
Issued refunds to affected customers

Lesson: Never store local time in a database. Always UTC.

War Story 2: The Y2038 Time Bomb in an Embedded System

What happened: A railway signaling system (installed in 2005) used 32-bit time_t for all timestamp operations. In 2023, during a routine firmware update, engineers discovered that maintenance schedules stored as Unix timestamps would overflow on January 19, 2038 — causing the signaling system to interpret future maintenance dates as being in 1901.

Impact: Would have caused all maintenance alerts to fail starting in 2038. No safety impact (trains wouldn't crash), but maintenance would be silently skipped.

Root cause: Original firmware used time_t (32-bit signed int) on a 32-bit ARM processor. All timestamp arithmetic would overflow at 2^31 - 1 = 2147483647.

Fix:

Rewrote timestamp handling to use 64-bit integers (int64_t)
Updated all stored timestamps (they were within 32-bit range, so no data loss)
Added automated tests that set the system clock to 2037, 2038, 2039, and 2050

Lesson: Audit embedded and legacy systems for Y2038 compliance now. Don't wait until 2037.

War Story 3: The JavaScript Millisecond Trap

What happened: A mobile app backend received timestamps from both an iOS app (Swift, sending Unix seconds) and a web dashboard (JavaScript, sending Unix milliseconds). The backend didn't normalize — it passed timestamps directly to the database. iOS timestamps were stored as 1705312200 (correct), web timestamps were stored as 1705312200000 (the year 56758 in the database's interpretation).

Impact: All web-originated records had dates 55,000+ years in the future. Reports were broken. Sorting was meaningless. Users saw "January 1, 56758" in the UI.

Root cause: No timestamp normalization layer. The API accepted both seconds and milliseconds without validation.

Fix:

Added a normalization layer: if timestamp > 1e12, treat as milliseconds; otherwise seconds
API contract updated: always send milliseconds (matching JavaScript convention)
Added input validation: reject timestamps outside a reasonable range (2000–2100)
Wrote a migration script to fix existing bad data

// The normalization function that should have been there from day one:
function normalizeTimestamp(ts) {
  if (ts > 1e12) return Math.floor(ts / 1000); // ms → s
  return ts; // already seconds
}

Lesson: Always normalize timestamp units at the API boundary. Never trust the client.

War Story 4: The Excel Date Import Disaster

What happened: A data analytics team imported an Excel file containing 50,000 rows of sales data into a PostgreSQL database. The dates were stored as Excel serial numbers (e.g., 45306.4375). The import script didn't account for Excel's 1900 leap year bug, so all dates after February 28, 1900 were off by one day.

Impact: 50,000 records with dates one day early. Quarterly reports showed sales on the wrong dates. A compliance audit flagged the discrepancy.

Root cause: The import script used datetime(1899, 12, 30) + timedelta(days=serial) without subtracting 1 for the phantom February 29, 1900.

Fix:

Corrected the import script to handle the leap year bug
Wrote a data migration to fix all affected records: UPDATE sales SET date = date + INTERVAL '1 day' WHERE date >= '1900-03-01'
Added unit tests with known Excel serial numbers and expected dates

Lesson: Always test date conversions with known values, especially for Excel. The 1900 leap year bug has bitten thousands of developers.

War Story 5: The Cloudflare Leap Second Crash

What happened: On January 1, 2017, Cloudflare's DNS resolver crashed on some servers. The root cause: Go code computed duration = time.Now() - lastUpdate. When the leap second caused time.Now() to go backward (repeating 23:59:59), duration became negative. The code called time.After(duration) with a negative duration, which panicked.

Impact: DNS resolution failures for ~1 hour on New Year's Day. Affected a small percentage of queries.

Root cause: Code assumed time.Now() is monotonically increasing. It's not — NTP can step the clock backward.

Fix: Cloudflare patched the code to use time.Since() (which uses a monotonic clock) instead of time.Now() - previous.

Lesson: Use monotonic clocks for measuring elapsed time. Never subtract two time.Now() calls.

War Story 6: The Timezone-Agnostic Scheduler

What happened: A job scheduler stored cron expressions with times in the server's local timezone. When the company migrated their server from US/Eastern to UTC (cloud migration), all scheduled jobs shifted by 5 hours. The 2 AM nightly backup started running at 9 PM instead.

Impact: Backups ran during peak traffic hours, causing performance degradation. One backup failed due to table locks.

Root cause: Cron expressions were timezone-dependent but the timezone wasn't explicitly stored. Moving the server changed the timezone context.

Fix:

Updated all cron expressions to UTC
Added explicit timezone to the scheduler configuration
Documented that all times are UTC

Lesson: Always specify the timezone explicitly for scheduled tasks. Never rely on the server's local timezone.

33. Troubleshooting Guide: Scenario-Based Diagnosis

"My timestamp is wrong." Here's how to find out why.

Scenario 1: "The date is off by a few hours"

Likely cause: Timezone mismatch.

Diagnosis steps:

Check what timezone the timestamp is stored in (UTC? Local? Mixed?)
Check what timezone the display layer assumes
Check if the offset matches a DST transition (off by exactly 1 hour = likely DST)
Check if the server timezone matches the expected timezone: date command on Linux, systeminfo on Windows

Quick fix: Ensure all storage is UTC, all display uses the user's local timezone.

# Linux: check server timezone
timedatectl

# Check if NTP is active
timedatectl | grep "NTP synchronized"

Likely cause: Fixed timezone offset not being applied.

Diagnosis steps:

Check the offset: if off by 8 hours, suspect UTC+8 (China/Singapore). If off by 5 hours, suspect US/Eastern.
Check if the API returns Z (UTC) but the client interprets it as local time
Check if new Date('2024-01-15T10:30:00') is being used without Z — JavaScript parses this as local time
Check if the database connection sets a session timezone (MySQL: SET time_zone = '+00:00')

Scenario 3: "The date is off by 1 day"

Likely cause: Excel serial date bug or date-boundary timezone issue.

Diagnosis steps:

If importing from Excel: check for the 1900 leap year bug (dates after Feb 28, 1900 off by 1 day)
If timezone-related: a timestamp at 2024-01-15T00:00:00+09:00 is 2024-01-14T15:00:00Z — the date changes when converted to UTC
Check date formatting: YYYY-DD-MM being parsed as YYYY-MM-DD

Scenario 4: "The timestamp shows a date in 1970"

Likely cause: Seconds vs milliseconds mismatch.

Diagnosis steps:

Check the number of digits: 10 digits = seconds, 13 digits = milliseconds
If you see new Date(1705312200) → this is Jan 20, 1970 (treated as milliseconds)
Fix: new Date(1705312200 * 1000)

Rule of thumb:

10 digits → seconds (Unix/C/Python default)
13 digits → milliseconds (JavaScript/Java default)
16 digits → microseconds
19 digits → nanoseconds

Scenario 5: "The timestamp shows a date thousands of years in the future"

Likely cause: Milliseconds treated as seconds, or vice versa.

Diagnosis steps:

If the year is ~55,000+: you passed milliseconds to a system expecting seconds
If the year is ~1970 and the timestamp has 13 digits: you passed seconds to a system expecting milliseconds
Check the API contract: does it specify seconds or milliseconds?

Scenario 6: "Some timestamps work, others don't"

Likely cause: Mixed format input, or DST boundary issue.

Diagnosis steps:

Check if the failing timestamps fall on DST transition dates (March/November in US; March/October in EU)
Check if the failing timestamps are in a different format (some rows ISO 8601, others Unix epoch)
Check for null/empty values mixed in with valid timestamps
Check if the timezone database is up-to-date: timedatectl on Linux, tzdata package version

Scenario 7: "The timestamp is correct in the database but wrong in the UI"

Likely cause: Client-side timezone rendering.

Diagnosis steps:

Check the raw API response (Network tab in browser DevTools) — is the timestamp correct there?
Check the JavaScript date parsing: new Date('2024-01-15T10:30:00Z') → .toString() shows local time
Check if the UI library applies a timezone filter that's misconfigured
Check if toLocaleString() or toLocaleDateString() is being called without a timezone argument

Fix: explicitly specify the timezone in the display layer:

new Date(ts).toLocaleString('en-US', { timeZone: 'America/New_York' });

Scenario 8: "Two servers show different timestamps for the same event"

Likely cause: Clock drift or different timezones.

Diagnosis steps:

Check each server's timezone: timedatectl (Linux) or systeminfo | findstr Time (Windows)
Check each server's clock: date -u (shows UTC time) on both servers — they should match
Check if NTP is running: timedatectl | grep "NTP synchronized"
Check if the timestamps are stored in different formats (one UTC, one local)

Scenario 9: "PostgreSQL `TIMESTAMPTZ` shows different times in different sessions"

This is expected behavior, not a bug. TIMESTAMPTZ stores UTC but displays in the session's timezone.

-- Session 1: timezone = UTC
SET time_zone = 'UTC';
SELECT created_at FROM events;
-- 2024-01-15 10:30:00+00

-- Session 2: timezone = America/New_York
SET time_zone = 'America/New_York';
SELECT created_at FROM events;
-- 2024-01-15 05:30:00-05  (same moment, different display)

Fix: always set the session timezone explicitly, or use AT TIME ZONE 'UTC' for consistent output.

Scenario 10: "Cron job runs at the wrong time after server migration"

Likely cause: Server timezone changed.

Diagnosis steps:

Check the old and new server timezone: timedatectl
Check if cron expressions were written in local time (e.g., 0 2 * * * = 2 AM local)
If the server moved from UTC-5 to UTC, 0 2 * * * now runs at 2 AM UTC (7 AM UTC-5)

Fix: set the server timezone to UTC and rewrite all cron expressions in UTC. Or use a scheduler that supports explicit timezone (e.g., Kubernetes CronJob timeZone field).

General debugging checklist

When you encounter a timestamp bug, walk through this checklist:

[ ] What format is the timestamp in? (Unix seconds? Milliseconds? ISO 8601? Excel serial?)
[ ] What timezone is the timestamp in? (UTC? Local? Unknown?)
[ ] Where was the timestamp created? (Client? Server? Database? Third-party API?)
[ ] What timezone does the storage layer assume? (MySQL session timezone? PostgreSQL TIMESTAMPTZ?)
[ ] What timezone does the display layer assume? (Browser local? Server local? Hardcoded?)
[ ] Is the server clock correct? (NTP synchronized? Clock drift?)
[ ] Is the timezone database current? (Last tzdata update? Recent DST rule changes?)
[ ] Does the timestamp fall on a DST transition? (Check the date against DST change dates)
[ ] Are seconds and milliseconds being mixed? (10-digit vs 13-digit timestamps)
[ ] Is the timestamp before 1970 or after 2038? (Negative or overflow issues)

34. FAQ

What timestamp format should I use for my API?

ISO 8601 with UTC (Z suffix). Example: 2024-01-15T10:30:00.000Z. It's human-readable, machine-parseable, timezone-explicit, and the de facto standard for modern web APIs.

What's the difference between Unix seconds and Unix milliseconds?

Seconds (10-digit, e.g., 1705312200) are used by C, Python, Go, and most Unix systems. Milliseconds (13-digit, e.g., 1705312200000) are used by JavaScript, Java, and most web APIs. Always check which one your system expects.

Why does Excel show numbers instead of dates?

Excel stores dates as serial numbers (days since December 30, 1899). If a cell formatted as a number contains a date serial, you'll see 45306 instead of January 15, 2024. Change the cell format to "Date" to fix this.

What's the Y2038 problem?

The 32-bit signed time_t (used by some older systems) overflows on January 19, 2038, at 03:14:07 UTC. After that, timestamps wrap to a negative number, causing dates to appear as December 1901 or similar. Most modern 64-bit systems are unaffected.

How do I convert between Unix timestamp and human-readable date?

Use our free Timestamp Converter — paste any Unix timestamp (seconds or milliseconds) and get the UTC, local, and ISO 8601 representation instantly. Or convert any date to a Unix timestamp.

How do I convert time between timezones?

Use our free Time Zone Converter — select source and target timezones, enter a date/time, and get the exact conversion. Includes a live world clock for major cities.

What's the difference between ISO 8601 and RFC 3339?

RFC 3339 is a subset of ISO 8601 designed for internet protocols. ISO 8601 allows many formats (week dates, ordinal dates, durations, basic format without separators). RFC 3339 narrows it to just YYYY-MM-DDTHH:MM:SS.fffZ or YYYY-MM-DDTHH:MM:SS.fff+HH:MM.

Should I store timestamps as UTC or local time in my database?

Always UTC. Store UTC, convert to local time only for display. This avoids DST issues, timezone confusion, and makes your data portable across regions.

What is the Windows FILETIME format?

It's a 64-bit value counting 100-nanosecond intervals since January 1, 1601 UTC. Used by Windows APIs, Active Directory, and LDAP. To convert to Unix: unix_seconds = filetime / 10000000 - 11644473600.

Why are there so many timestamp formats?

Because time is fundamental to computing, and every system, language, and era invented its own solution. Unix chose 1970. Windows chose 1601. Excel chose 1899. Apple chose 2001. Astronomers chose 4713 BC. Each made sense in its context. The result is the mess we have today — which is why this guide exists.

What's the difference between `time.time()` and `time.monotonic()` in Python?

time.time() returns wall-clock time (can go backward if NTP adjusts the clock, affected by leap seconds). time.monotonic() returns a clock that only goes forward — it's not affected by system time changes. Use time.monotonic() for measuring elapsed time. Use time.time() for recording when an event happened.

How do I handle leap seconds in my application?

If you're on a major cloud provider (AWS, GCP, Azure), they use leap smearing — your code never sees the discontinuity. If you're on bare metal, use chronyd with smear mode. For measuring durations, always use monotonic clocks (time.monotonic(), performance.now(), CLOCK_MONOTONIC). Never assume time.Now() is monotonically increasing — NTP can step the clock backward.

Should I use `TIMESTAMP` or `DATETIME` in MySQL?

Use DATETIME for dates beyond 2038 (it supports years 1000–9999). Use TIMESTAMP only if you need automatic timezone conversion (it converts to UTC on storage and back to the session timezone on retrieval, but only supports 1970–2038). For new projects, DATETIME with explicit UTC storage is the safer choice.

How do I debug a timestamp issue?

Start with the troubleshooting checklist in Section 33. The most common causes are: (1) seconds vs milliseconds mismatch, (2) timezone mismatch between storage and display, (3) DST transition edge cases, (4) Excel serial date conversion bugs. Walk through the 10-item debugging checklist to isolate the root cause.

Online tools vs command-line tools for timestamp conversion?

Both have their place:

Online tools (like our Timestamp Converter) are best for quick one-off conversions — just paste a timestamp and get the result. No installation, no syntax to remember. Ideal for debugging API responses, checking log files, or verifying data imports.
Command-line tools are better for scripting and automation. date -d @1705312200 (Linux), date -r 1705312200 (macOS), or [DateTimeOffset]::FromUnixTimeSeconds(1705312200) (PowerShell) work well in pipelines and cron jobs.
Language-native libraries are best for application code — they handle edge cases (DST, leap seconds, timezone databases) that ad-hoc parsing misses.

Use whichever fits the task. For a quick check, an online tool is faster. For a script, use the command line. For production code, always use a well-tested library.

Tools for Working with Timestamps

Online tools (browser-based, no installation)

Timestamp Converter — Convert Unix timestamps (seconds/ms) to human-readable dates and vice versa. Shows UTC, local, ISO 8601, and relative time. Runs entirely in your browser.
Time Zone Converter — Convert any time between timezones with a live world clock. Calculate time differences between zones. Also browser-based.
JSON Formatter — Useful for inspecting timestamps in JSON API responses.
JWT Decoder — Decode JWT tokens to inspect exp, iat, and nbf timestamp claims.

Command-line quick reference

# Linux / macOS: convert Unix timestamp to human-readable
date -d @1705312200          # GNU date (Linux)
date -r 1705312200            # BSD date (macOS)

# Convert to Unix timestamp
date -d '2024-01-15 10:30:00 UTC' +%s

# Current Unix timestamp
date +%s

# PowerShell
[DateTimeOffset]::FromUnixTimeSeconds(1705312200)
[DateTimeOffset]::UtcNow.ToUnixTimeSeconds()

All ToolJar tools run entirely in your browser — no data is sent to any server.

This guide covers every major timestamp format in computing, from Unix epoch to Excel serial numbers, from ISO 8601 to GPS time. If you find a format we missed, let us know. Bookmark this page for reference — you'll be back.

Published on ToolJar — 70+ free browser-based tools. No downloads, no sign-ups, no uploads.

The Complete Guide to Timestamp Formats: Every Time Format Explained

Table of Contents

1. Unix Epoch Time

What it is

Why it exists

The four precisions

The 2038 Problem

How to read it

Quick conversion (in code)

2. ISO 8601

What it is

Why it exists

Core formats

The T separator

The Z suffix

Basic vs Extended format

Why you should use it

3. RFC 3339

What it is

How it differs from ISO 8601

Standard format

Why it exists

Example in a JSON API

4. RFC 2822 / Email Date Format

What it is

Format

Example

Where you'll see it

Why it exists

Parsing in code

5. HTTP Date Format (RFC 7231)

What it is

Format

Example

Key difference from RFC 2822

Where you'll see it

Why it exists

6. Windows FILETIME / LDAP Timestamps

What it is

Why 1601?

Example

Where you'll see it

Converting FILETIME to Unix timestamp

LDAP timestamp

7. .NET DateTime Ticks

What it is

Example

Where you'll see it

DateTimeKind matters

Converting ticks to Unix timestamp

8. Excel Serial Date Numbers

What it is

Example

The 1900 leap year bug

Excel for Mac (1904 epoch)

Where you'll see it

Converting Excel serial to date

Google Sheets

9. C / POSIX time_t

What it is

Usage

Type confusion

Related: struct tm

strftime for formatting

10. JavaScript Date.now()

What it is

Example

Why milliseconds, not seconds?

Common gotcha: seconds vs milliseconds

toISOString() output

11. Java System.currentTimeMillis()

What it is

Example

java.time API (Java 8+)

Joda-Time (legacy)

12. Python time.time()

What it is

Example

datetime module

time.time() vs datetime.now()

The `T` separator

The `Z` suffix

`DateTimeKind` matters

9. C / POSIX `time_t`

Related: `struct tm`

`strftime` for formatting

10. JavaScript `Date.now()`

`toISOString()` output

11. Java `System.currentTimeMillis()`

`java.time` API (Java 8+)

12. Python `time.time()`

`datetime` module

`time.time()` vs `datetime.now()`

13. Go `time.Time`

20. ANSI C `asctime()` Format