Date, Time, UUID, and Array Types

PostgreSQL has first-class support for temporal data with types that handle dates, times, and time zones correctly. DATE stores a calendar date without time, TIME stores a time of day, TIMESTAMP stores date and time without time zone, and TIMESTAMPTZ (timestamp with time zone) stores a UTC value and displays it in the session time zone — always prefer TIMESTAMPTZ for application data. INTERVAL represents a duration and supports arithmetic with other date/time values. The UUID type stores 128-bit universally unique identifiers and is an excellent alternative to SERIAL for distributed systems where you cannot coordinate auto-increment across multiple databases. PostgreSQL arrays allow storing multiple values of the same type in a single column, with dedicated operators and functions for querying array contents.

Example

-- Temporal types
CREATE TABLE events (
  id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  event_date   DATE         NOT NULL,
  starts_at    TIMETZ       NOT NULL,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  duration     INTERVAL
);

-- Insert with various date literals
INSERT INTO events (event_date, starts_at, duration)
VALUES
  ('2024-06-15', '14:30:00+00', INTERVAL '2 hours 30 minutes'),
  (CURRENT_DATE, NOW()::TIME, '90 minutes');

-- Date arithmetic
SELECT
  NOW() + INTERVAL '7 days'             AS next_week,
  NOW() - INTERVAL '1 month'            AS last_month,
  AGE(TIMESTAMP '2024-01-01', NOW())     AS time_since,
  DATE_TRUNC('month', NOW())             AS start_of_month,
  EXTRACT(DOW FROM NOW())               AS day_of_week;  -- 0=Sun

-- Array type
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  name  TEXT,
  tags  TEXT[],        -- array of text
  scores INTEGER[]     -- array of integers
);

INSERT INTO users (name, tags, scores)
VALUES ('Alice', ARRAY['admin', 'editor'], ARRAY[95, 87, 92]);

-- Array operators
SELECT * FROM users WHERE 'admin' = ANY(tags);
SELECT name, tags[1] AS first_tag FROM users;
SELECT name, array_length(scores, 1) AS num_scores FROM users;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned