Here's a comparison of data types and casting functions in PostgreSQL, BigQuery (Standard SQL), and ISO SQL, specifically focusing on string and datetime handling, including casting functions and date truncation.
Data Types
| Feature | PostgreSQL | BigQuery (Standard SQL) | ISO SQL (SQL-2016) |
|---|
| String Types | TEXT, VARCHAR(n), CHAR(n) | STRING | CHAR(n), VARCHAR(n), CLOB |
| Datetime Types | DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL | DATE, DATETIME, TIMESTAMP, TIME | DATE, TIME, TIMESTAMP, INTERVAL |
| Time Zones | TIMESTAMPTZ for UTC conversion | TIMESTAMP (implicitly UTC), DATETIME (no timezone) | TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE |
| Intervals | INTERVAL type (e.g., INTERVAL '1 day') | No explicit INTERVAL, but can use TIMESTAMP_DIFF and TIMESTAMP_ADD | INTERVAL type (similar to PostgreSQL) |
Casting Functions
| Function | PostgreSQL | BigQuery (Standard SQL) | ISO SQL |
|---|
| Explicit Cast | CAST(value AS target_type) or value::target_type | CAST(value AS target_type) or SAFE_CAST | CAST(value AS target_type) |
| String to Integer | '123'::INTEGER or CAST('123' AS INTEGER) | CAST('123' AS INT64) or SAFE_CAST | CAST('123' AS INTEGER) |
| String to Float | '12.34'::FLOAT or CAST('12.34' AS FLOAT) | CAST('12.34' AS FLOAT64) | CAST('12.34' AS FLOAT) |
| String to Date | CAST('2024-01-01' AS DATE) or '2024-01-01'::DATE | CAST('2024-01-01' AS DATE) or PARSE_DATE('%Y-%m-%d', '2024-01-01') | CAST('2024-01-01' AS DATE) |
| String to Timestamp | CAST('2024-01-01 12:34:56' AS TIMESTAMP) or '2024-01-01 12:34:56'::TIMESTAMP | CAST('2024-01-01 12:34:56' AS TIMESTAMP) or PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-01-01 12:34:56') | CAST('2024-01-01 12:34:56' AS TIMESTAMP) |
| Date to String | TO_CHAR(DATE '2024-01-01', 'YYYY-MM-DD') | FORMAT_DATE('%Y-%m-%d', DATE '2024-01-01') | CAST(DATE '2024-01-01' AS CHAR) |
Date Truncation (DATE_TRUNC and equivalents)
| Function | PostgreSQL | BigQuery (Standard SQL) | ISO SQL |
|---|
| Truncate Date | DATE_TRUNC('month', DATE '2024-03-15') → 2024-03-01 | DATE_TRUNC(DATE '2024-03-15', MONTH) → 2024-03-01 | TRUNC(DATE '2024-03-15', 'MONTH') |
| Truncate Timestamp | DATE_TRUNC('hour', TIMESTAMP '2024-03-15 14:45:00') → 2024-03-15 14:00:00 | TIMESTAMP_TRUNC(TIMESTAMP '2024-03-15 14:45:00', HOUR) → 2024-03-15 14:00:00 UTC | TRUNC(TIMESTAMP '2024-03-15 14:45:00', 'HOUR') |
| Truncate to Year | DATE_TRUNC('year', DATE '2024-03-15') → 2024-01-01 | DATE_TRUNC(DATE '2024-03-15', YEAR) → 2024-01-01 | TRUNC(DATE '2024-03-15', 'YEAR') |
Extracting Date Components
| Function | PostgreSQL | BigQuery (Standard SQL) | ISO SQL |
|---|
| Extract Year | EXTRACT(YEAR FROM DATE '2024-03-15') | EXTRACT(YEAR FROM DATE '2024-03-15') | EXTRACT(YEAR FROM DATE '2024-03-15') |
| Extract Month | EXTRACT(MONTH FROM DATE '2024-03-15') | EXTRACT(MONTH FROM DATE '2024-03-15') | EXTRACT(MONTH FROM DATE '2024-03-15') |
| Extract Day | EXTRACT(DAY FROM DATE '2024-03-15') | EXTRACT(DAY FROM DATE '2024-03-15') | EXTRACT(DAY FROM DATE '2024-03-15') |
| Extract Hour | EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 14:45:00') | EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 14:45:00') | EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 14:45:00') |
Adding and Subtracting Dates
| Function | PostgreSQL | BigQuery (Standard SQL) | ISO SQL |
|---|
| Add Days | DATE '2024-03-15' + INTERVAL '5 days' | DATE_ADD(DATE '2024-03-15', INTERVAL 5 DAY) | DATE '2024-03-15' + INTERVAL '5' DAY |
| Subtract Days | DATE '2024-03-15' - INTERVAL '5 days' | DATE_SUB(DATE '2024-03-15', INTERVAL 5 DAY) | DATE '2024-03-15' - INTERVAL '5' DAY |
| Add Months | DATE '2024-03-15' + INTERVAL '1 month' | DATE_ADD(DATE '2024-03-15', INTERVAL 1 MONTH) | DATE '2024-03-15' + INTERVAL '1' MONTH |
| Add Hours | TIMESTAMP '2024-03-15 14:45:00' + INTERVAL '2 hours' | TIMESTAMP_ADD(TIMESTAMP '2024-03-15 14:45:00', INTERVAL 2 HOUR) | TIMESTAMP '2024-03-15 14:45:00' + INTERVAL '2' HOUR |
Summary
- PostgreSQL has a flexible casting system (
value::type syntax) and rich date manipulation functions with INTERVAL and DATE_TRUNC.
- BigQuery follows a more explicit syntax for casting (
SAFE_CAST) and date functions (TIMESTAMP_TRUNC, DATE_ADD).
- ISO SQL is more general, supporting standard
CAST, EXTRACT, and INTERVAL, but specific syntax like TRUNC(date, 'unit') is not as widely supported in real-world databases.