Dates & Time Functions

SELECT
  NOW() as processed_at,
  event_time,
  NOW() - event_time as latency
FROM events

SELECT
  EXTRACT(YEAR FROM timestamp) as year,
  EXTRACT(MONTH FROM timestamp) as month,
  EXTRACT(DAY FROM timestamp) as day,
  EXTRACT(HOUR FROM timestamp) as hour,
  EXTRACT(MINUTE FROM timestamp) as minute,
  EXTRACT(SECOND FROM timestamp) as second,
  EXTRACT(DOW FROM timestamp) as day_of_week,    -- 0=Sunday
  EXTRACT(DOY FROM timestamp) as day_of_year,
  EXTRACT(WEEK FROM timestamp) as week_number,
  EXTRACT(QUARTER FROM timestamp) as quarter
FROM events

SELECT
  DATE_PART('hour', timestamp) as hour,
  DATE_PART('minute', timestamp) as minute
FROM events

SELECT
  DATE_TRUNC('minute', timestamp) as minute_start,
  DATE_TRUNC('hour', timestamp) as hour_start,
  DATE_TRUNC('day', timestamp) as day_start,
  DATE_TRUNC('week', timestamp) as week_start,
  DATE_TRUNC('month', timestamp) as month_start
FROM events

SELECT
  DATE_TRUNC('hour', event_time) as hour,
  COUNT(*) as events
FROM logs
GROUP BY DATE_TRUNC('hour', event_time), TUMBLE(interval '1 hour')

SELECT
  timestamp + INTERVAL '1 hour' as plus_hour,
  timestamp + INTERVAL '30 minutes' as plus_30min,
  timestamp + INTERVAL '7 days' as plus_week,
  timestamp - INTERVAL '1 month' as minus_month
FROM events

SELECT
  order_time,
  ship_time,
  ship_time - order_time as fulfillment_time
FROM orders

SELECT
  TO_CHAR(timestamp, 'YYYY-MM-DD') as date_str,
  TO_CHAR(timestamp, 'HH24:MI:SS') as time_str,
  TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') as datetime_str,
  TO_CHAR(timestamp, 'Day, Month DD, YYYY') as readable
FROM events

SELECT
  TO_TIMESTAMP('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') as ts,
  TO_TIMESTAMP(epoch_millis / 1000) as from_epoch
FROM raw_data

SELECT
  CAST('2024-03-15' as TIMESTAMP) as ts,
  CAST('2024-03-15' as DATE) as date_only,
  timestamp::DATE as date_from_ts
FROM events

SELECT
  -- Timestamp to epoch seconds
  EXTRACT(EPOCH FROM timestamp) as epoch_seconds,
 
  -- Epoch to timestamp
  TO_TIMESTAMP(epoch_seconds) as timestamp,
 
  -- Milliseconds to timestamp
  TO_TIMESTAMP(epoch_millis / 1000) as timestamp
FROM events

SELECT
  timestamp AT TIME ZONE 'UTC' as utc_time,
  timestamp AT TIME ZONE 'America/New_York' as eastern_time,
  timestamp AT TIME ZONE 'Europe/London' as london_time
FROM events

-- Events from today
SELECT * FROM events
WHERE timestamp >= DATE_TRUNC('day', NOW())
 
-- Events in the last hour
SELECT * FROM events
WHERE timestamp >= NOW() - INTERVAL '1 hour'
 
-- Business hours only
SELECT * FROM orders
WHERE EXTRACT(HOUR FROM timestamp) BETWEEN 9 AND 17
  AND EXTRACT(DOW FROM timestamp) BETWEEN 1 AND 5

SELECT
  session_id,
  MIN(timestamp) as start_time,
  MAX(timestamp) as end_time,
  MAX(timestamp) - MIN(timestamp) as duration,
  EXTRACT(EPOCH FROM MAX(timestamp) - MIN(timestamp)) as duration_seconds
FROM events
GROUP BY session_id, SESSION(interval '30 minutes')

SELECT
  CASE
    WHEN EXTRACT(HOUR FROM timestamp) < 6 THEN 'night'
    WHEN EXTRACT(HOUR FROM timestamp) < 12 THEN 'morning'
    WHEN EXTRACT(HOUR FROM timestamp) < 18 THEN 'afternoon'
    ELSE 'evening'
  END as time_of_day,
  COUNT(*) as events
FROM user_activity
GROUP BY 1, TUMBLE(interval '1 day')

SELECT
  DATE_TRUNC('day', timestamp) as day,
  COUNT(*) as events_today,
  LAG(COUNT(*), 7) OVER (ORDER BY DATE_TRUNC('day', timestamp)) as events_last_week
FROM events
GROUP BY DATE_TRUNC('day', timestamp), TUMBLE(interval '1 day')

SELECT
  TUMBLE(interval '1 minute') as window,
  AVG(EXTRACT(EPOCH FROM processed_at - event_time)) as avg_latency_sec,
  MAX(EXTRACT(EPOCH FROM processed_at - event_time)) as max_latency_sec,
  APPROX_PERCENTILE_CONT(
    EXTRACT(EPOCH FROM processed_at - event_time),
    0.99
  ) as p99_latency_sec
FROM events
GROUP BY window