Current date, time, and timestamp

-- Today's date (no time component).
SELECT CURRENT_DATE;

-- Right now (date + time + sometimes timezone).
SELECT CURRENT_TIMESTAMP;
SELECT NOW();                 -- Postgres / MySQL / DuckDB
SELECT datetime('now');       -- SQLite
SELECT CURRENT_TIMESTAMP();   -- BigQuery (note parens)
SELECT GETDATE();             -- SQL Server (local server time)
SELECT SYSDATETIME();         -- SQL Server (higher precision, server time)
SELECT SYSDATE FROM DUAL;     -- Oracle (local server time)

-- Time only.
SELECT CURRENT_TIME;          -- Most engines
SELECT LOCALTIME;             -- Postgres / DuckDB

-- UTC explicitly (when your CURRENT_TIMESTAMP is local).
SELECT NOW() AT TIME ZONE 'UTC';                        -- Postgres
SELECT UTC_TIMESTAMP();                                 -- MySQL
SELECT GETUTCDATE();                                    -- SQL Server
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';            -- ANSI

-- "Last 7 days" filter.
SELECT * FROM orders
WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '7 days';

-- "Today's orders" filter (avoid wrapping a column in a function).
SELECT * FROM orders
WHERE order_date = CURRENT_DATE;            -- index-friendly

-- "This month so far".
SELECT * FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- Default to NOW() in a column definition (audit timestamps).
CREATE TABLE events (
  id         INTEGER PRIMARY KEY,
  payload    VARCHAR,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Postgres-specific demo: NOW() returns transaction start; CLOCK_TIMESTAMP() the wall clock.
BEGIN;
  SELECT NOW();              -- e.g. 14:30:00
  SELECT pg_sleep(2);
  SELECT NOW();              -- still 14:30:00 (transaction start)
  SELECT CLOCK_TIMESTAMP();  -- 14:30:02 — actual wall clock
COMMIT;