SQL Timestamp Functions Reference

Quick reference for timestamp operations across popular database systems.

Current Timestamp

Database	Current Timestamp (UTC)	Current Timestamp (Local)
MySQL	NOW(), UTC_TIMESTAMP()	NOW(), LOCALTIMESTAMP()
PostgreSQL	NOW(), CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP
SQLite	datetime('now')	datetime('now', 'localtime')
SQL Server	GETUTCDATE()	GETDATE(), SYSDATETIME()
Oracle	SYSTIMESTAMP, SYS_EXTRACT_UTC(SYSTIMESTAMP)	SYSTIMESTAMP

Unix Timestamp Operations

Get Current Unix Timestamp

Database	Code
MySQL	UNIX_TIMESTAMP()
PostgreSQL	EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
SQLite	strftime('%s', 'now')
SQL Server	DATEDIFF(s, '1970-01-01', GETUTCDATE())
Oracle	(SYSTIMESTAMP - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD')) * 86400

Convert Unix Timestamp to Date

Database	Code
MySQL	FROM_UNIXTIME(1704067200)
PostgreSQL	TO_TIMESTAMP(1704067200)
SQLite	datetime(1704067200, 'unixepoch')
SQL Server	DATEADD(s, 1704067200, '1970-01-01')
Oracle	TO_DATE('1970-01-01', 'YYYY-MM-DD') + (1704067200 / 86400)

Convert Date to Unix Timestamp

Database	Code
MySQL	UNIX_TIMESTAMP('2024-01-01 00:00:00')
PostgreSQL	EXTRACT(EPOCH FROM '2024-01-01 00:00:00'::timestamp)
SQLite	strftime('%s', '2024-01-01 00:00:00')
SQL Server	DATEDIFF(s, '1970-01-01', '2024-01-01 00:00:00')
Oracle	TO_CHAR('2024-01-01 00:00:00', 'J') - TO_CHAR(DATE '1970-01-01', 'J')) * 86400

Date Formatting

Common Date Formats

Database	ISO 8601	US Format	EU Format
MySQL	DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%s')	DATE_FORMAT(NOW(), '%m/%d/%Y')	DATE_FORMAT(NOW(), '%d/%m/%Y')
PostgreSQL	TO_CHAR(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS')	TO_CHAR(NOW(), 'MM/DD/YYYY')	TO_CHAR(NOW(), 'DD/MM/YYYY')
SQLite	datetime('now', 'iso8601')	-	-
SQL Server	FORMAT(GETUTCDATE(), 'yyyy-MM-ddTHH:mm:ss')	FORMAT(GETDATE(), 'MM/dd/yyyy')	FORMAT(GETDATE(), 'dd/MM/yyyy')
Oracle	TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS')	TO_CHAR(SYSDATE, 'MM/DD/YYYY')	TO_CHAR(SYSDATE, 'DD/MM/YYYY')

Timezone Operations

Get Current Timezone

Database	Code
MySQL	TIMEZONE_NAME, @@time_zone
PostgreSQL	current_setting('timezone')
SQLite	N/A (no timezone support)
SQL Server	GETDATE(), DATENAME(TIMEZONE, SYSDATETIMEOFFSET())
Oracle	SESSIONTIMEZONE, DBTIMEZONE

Convert Timezone

Database	Code
MySQL	CONVERT_TZ('2024-01-01 12:00:00', 'UTC', 'America/New_York')
PostgreSQL	'2024-01-01 12:00:00 UTC'::timestamptz AT TIME ZONE 'America/New_York'
SQLite	N/A
SQL Server	GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'
Oracle	FROM_TZ(TIMESTAMP '2024-01-01 12:00:00', 'UTC') AT TIME ZONE 'America/New_York'

Timestamp Arithmetic

Add/Subtract Time

Database	Add 1 Day	Subtract 1 Hour
MySQL	DATE_ADD(NOW(), INTERVAL 1 DAY)	DATE_SUB(NOW(), INTERVAL 1 HOUR)
PostgreSQL	NOW() + INTERVAL '1 day'	NOW() - INTERVAL '1 hour'
SQLite	datetime('now', '+1 day')	datetime('now', '-1 hour')
SQL Server	DATEADD(day, 1, GETDATE())	DATEADD(hour, -1, GETDATE())
Oracle	SYSDATE + 1	SYSDATE - (1/24)

Calculate Difference

Database	Days Between
MySQL	DATEDIFF('2024-01-15', '2024-01-01')
PostgreSQL	'2024-01-15'::date - '2024-01-01'::date
SQLite	julianday('2024-01-15') - julianday('2024-01-01')
SQL Server	DATEDIFF(day, '2024-01-01', '2024-01-15')
Oracle	TO_DATE('2024-01-15') - TO_DATE('2024-01-01')

Best Practices

1. Store Timestamps in UTC

Always store timestamps in UTC format and convert to local time only when displaying to users.

-- Good: Store in UTC
CREATE TABLE events (
    id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Better: Explicit UTC
CREATE TABLE events (
    id INT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

2. Use Appropriate Data Types

Use Case	Recommended Type
UTC storage	TIMESTAMP WITH TIME ZONE
Local time only	TIMESTAMP WITHOUT TIME ZONE
Unix timestamp	BIGINT (for portability)
Date only	DATE

3. Handle Timezone Conversions Properly

-- PostgreSQL example
SELECT 
    created_at AT TIME ZONE 'UTC' AS utc_time,
    created_at AT TIME ZONE 'America/New_York' AS ny_time,
    created_at AT TIME ZONE 'Asia/Shanghai' AS shanghai_time
FROM events;

Related Tools

• Unix Timestamp Converter
• MySQL Timestamp
• PostgreSQL Timestamp
• SQLite Timestamp