Can my DB?

-- Store timezone-aware timestamps (always UTC internally)
CREATE TABLE events (
  id       SERIAL PRIMARY KEY,
  name     TEXT,
  event_at TIMESTAMP WITH TIME ZONE  -- timestamptz
);

-- Insert with explicit timezone
INSERT INTO events (name, event_at)
VALUES ('Launch', '2024-06-15 09:00:00 America/New_York');

-- Convert to a specific timezone for display
SELECT name,
       event_at AT TIME ZONE 'America/New_York' AS eastern,
       event_at AT TIME ZONE 'Europe/London'    AS london
FROM events;

-- Set session timezone
SET timezone = 'America/Chicago';
SELECT NOW();  -- displayed in Chicago time

• timestamptz stores UTC internally; all conversions happen at display time.
• Timezone names use the IANA timezone database (e.g., 'America/New_York').
• AT TIME ZONE operator converts a timestamptz to a specific timezone.
• NOW() and CURRENT_TIMESTAMP return timestamptz.
• TIMESTAMP WITHOUT TIME ZONE (timestamp) stores a literal date/time with no zone context.

-- TIMESTAMP: stored as UTC, displayed in session timezone
CREATE TABLE events (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100),
  event_at TIMESTAMP  -- UTC storage, session-tz display
);

-- Set session timezone
SET time_zone = 'America/New_York';

-- Convert between timezones
SELECT CONVERT_TZ('2024-06-15 09:00:00', 'America/New_York', 'UTC') AS utc_time;

-- DATETIME has no timezone awareness
CREATE TABLE events2 (
  event_at DATETIME  -- stores literal date/time, no timezone
);

• TIMESTAMP type: stored as UTC, automatically converted to/from session timezone on read/write.
• TIMESTAMP range is limited: 1970-01-01 to 2038-01-19.
• DATETIME type: stores literal date/time without any timezone awareness.
• CONVERT_TZ() requires timezone tables loaded via mysql_tzinfo_to_sql.
• Named timezone support requires the mysql.time_zone tables to be populated.

SET time_zone = 'America/New_York';

SELECT CONVERT_TZ(NOW(), 'America/New_York', 'UTC') AS utc_time;

CREATE TABLE events (
  event_at TIMESTAMP  -- stored UTC, displayed in session timezone
);

• Same TIMESTAMP (UTC-stored) and DATETIME (literal) behavior as MySQL.
• CONVERT_TZ() function for timezone conversion.
• Named timezone support via mysql_tzinfo_to_sql utility.
• time_zone system variable controls session display timezone.

-- TIMESTAMP WITH TIME ZONE stores value + offset
CREATE TABLE events (
  id       NUMBER PRIMARY KEY,
  name     VARCHAR2(100),
  event_at TIMESTAMP WITH TIME ZONE
);

-- Insert with timezone
INSERT INTO events VALUES (1, 'Launch',
  TIMESTAMP '2024-06-15 09:00:00 America/New_York');

-- AT TIME ZONE conversion
SELECT name,
       event_at AT TIME ZONE 'UTC'          AS utc_time,
       event_at AT TIME ZONE 'Europe/London' AS london_time
FROM events;

-- TIMESTAMP WITH LOCAL TIME ZONE normalizes to DB timezone
CREATE TABLE logs (
  logged_at TIMESTAMP WITH LOCAL TIME ZONE
);

• TIMESTAMP WITH TIME ZONE stores the actual offset/timezone along with the value.
• TIMESTAMP WITH LOCAL TIME ZONE normalizes to the database timezone on storage.
• FROM_TZ(timestamp, 'timezone') converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE.
• SYS_EXTRACT_UTC() extracts the UTC equivalent.
• AT TIME ZONE operator for conversion between zones.

-- DATETIMEOFFSET: stores value with UTC offset
CREATE TABLE events (
  id       INT IDENTITY PRIMARY KEY,
  name     NVARCHAR(100),
  event_at DATETIMEOFFSET(3)
);

-- AT TIME ZONE conversion (SQL Server 2016+)
SELECT name,
  event_at AT TIME ZONE 'Eastern Standard Time' AS eastern,
  event_at AT TIME ZONE 'UTC'                   AS utc_time
FROM events;

-- Add offset to DATETIME2
SELECT TODATETIMEOFFSET(GETDATE(), '-05:00');

-- Switch offset
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00');

• DATETIMEOFFSET stores both the date/time value and its UTC offset.
• AT TIME ZONE operator added in SQL Server 2016 for conversion between named zones.
• Uses Windows timezone names (e.g., 'Eastern Standard Time'), not IANA names.
• TODATETIMEOFFSET() adds an offset to a DATETIME2 value.
• Pre-2016: manual DATEADD arithmetic required for timezone conversion.

-- SQLite stores dates as TEXT, INTEGER, or REAL
-- ISO 8601 text format
CREATE TABLE events (
  event_at TEXT  -- e.g., '2024-06-15T14:00:00Z'
);

-- Current UTC time as Unix epoch
SELECT strftime('%s', 'now') AS utc_epoch;

-- Current local time
SELECT datetime('now', 'localtime') AS local_time;

-- Convert epoch to ISO 8601
SELECT datetime(event_epoch, 'unixepoch') FROM events;

• No native timezone-aware timestamp type.
• Dates stored as TEXT (ISO 8601), INTEGER (Unix epoch), or REAL (Julian day).
• datetime('now') returns UTC; datetime('now', 'localtime') applies system timezone.
• No AT TIME ZONE or named timezone support in SQL.
• Applications must handle timezone conversion outside the database.

-- TIMESTAMPTZ stores UTC
CREATE TABLE events (
  event_at TIMESTAMPTZ
);

-- CONVERT_TIMEZONE for conversion
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', event_at) AS eastern
FROM events;

-- AT TIME ZONE syntax
SELECT event_at AT TIME ZONE 'America/Chicago'
FROM events;

-- Current time functions
SELECT CURRENT_TIMESTAMP,  -- UTC
       GETDATE(),           -- local (cluster) time
       SYSDATE;             -- UTC

• TIMESTAMPTZ stores UTC; TIMESTAMP stores literal date/time without timezone.
• CONVERT_TIMEZONE(source_tz, target_tz, timestamp) for explicit conversion.
• AT TIME ZONE syntax also supported.
• Uses IANA timezone names.
• CURRENT_TIMESTAMP returns UTC; GETDATE() returns cluster local time.

-- TIMESTAMP WITH TIME ZONE (DB2 9.1+)
CREATE TABLE events (
  event_at TIMESTAMP(6) WITH TIME ZONE
);

-- Current timestamp in session timezone
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

-- AT TIME ZONE conversion
SELECT event_at AT TIME ZONE 'America/New_York'
FROM events;

-- Session timezone
SELECT CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY1;

• TIMESTAMP WITH TIME ZONE available since DB2 9.1.
• CURRENT TIMEZONE special register returns the session's UTC offset.
• AT TIME ZONE syntax for conversion between named zones.
• CURRENT TIMESTAMP returns the current timestamp in session timezone.
• Uses IANA timezone names for named timezone support.

-- Three timestamp types
CREATE TABLE events (
  event_tz   TIMESTAMP_TZ,   -- stores value + offset
  event_ltz  TIMESTAMP_LTZ,  -- local (session) timezone
  event_ntz  TIMESTAMP_NTZ   -- no timezone (naive)
);

-- CONVERT_TIMEZONE
SELECT CONVERT_TIMEZONE('America/New_York', 'UTC', event_tz) AS utc_time
FROM events;

-- Session timezone
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

SELECT CURRENT_TIMESTAMP;  -- returns TIMESTAMP_LTZ in session timezone

• TIMESTAMP_TZ: stores value with UTC offset (timezone-aware).
• TIMESTAMP_LTZ: stores UTC internally, displays in session/account timezone.
• TIMESTAMP_NTZ: stores literal date/time with no timezone (default TIMESTAMP type).
• CONVERT_TIMEZONE(src_tz, tgt_tz, ts) for explicit conversion.
• Account and session TIMEZONE parameters control LTZ display.

-- TIMESTAMP stores UTC; DATETIME is timezone-naive
CREATE TABLE events (
  event_at  TIMESTAMP,  -- UTC
  local_dt  DATETIME    -- no timezone
);

-- AT TIME ZONE for conversion
SELECT event_at AT TIME ZONE 'America/New_York' AS eastern
FROM events;

-- FORMAT_TIMESTAMP for display in specific timezone
SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', event_at, 'Europe/London') AS london
FROM events;

-- PARSE_TIMESTAMP with timezone
SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-06-15 09:00:00', 'America/New_York');

• TIMESTAMP type always stores UTC; displayed/converted on demand.
• DATETIME type stores a literal date/time with no timezone information.
• AT TIME ZONE operator converts TIMESTAMP to DATETIME in the specified zone.
• FORMAT_TIMESTAMP(format, ts, tz) renders a timestamp in a specific timezone.
• Uses IANA timezone names throughout.
• CURRENT_TIMESTAMP() returns UTC.