How to Generate Random Dates in PostgreSQL (With Example)

Often in PostgreSQL you may want to generate random dates in a date range.

You can use the following syntax to do so:

SELECT *,
       (timestamp '2024-01-01' +
       random() * (timestamp '2024-12-31' -
                   timestamp '2024-01-01'))::date AS random_date
FROM athletes;

This particular example will create a new column named random_date that contains a random date between the following two dates:

• 2024-01-01
• 2024-12-31

To use a different date range, simply change the starting and ending date values in the query to different dates.

The following example shows how to use this syntax in practice.

Example: How to Generate Random Dates in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Celtics', 40);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Nuggets', 19);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

-- view all rows in table
SELECT * FROM athletes;

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     22
  2 | Hawks   |     30
  3 | Kings   |     12
  4 | Kings   |     25
  5 | Mavs    |     31
  6 | Mavs    |     16
  7 | Celtics |     40
  8 | Celtics |     14
  9 | Nuggets |     19
 10 | Hawks   |     26

Suppose that we would like to add a new column to the table that contains a random date between 2024-01-01 and 2024-12-31.

We can use the following syntax to do so:

SELECT *,
       (timestamp '2024-01-01' +
       random() * (timestamp '2024-12-31' -
                   timestamp '2024-01-01'))::date AS random_date
FROM athletes;

 id |  team   | points | random_date 
----+---------+--------+-------------
  1 | Mavs    |     22 | 2024-10-04
  2 | Hawks   |     30 | 2024-07-10
  3 | Kings   |     12 | 2024-11-23
  4 | Kings   |     25 | 2024-05-14
  5 | Mavs    |     31 | 2024-07-31
  6 | Mavs    |     16 | 2024-02-18
  7 | Celtics |     40 | 2024-08-29
  8 | Celtics |     14 | 2024-10-13
  9 | Nuggets |     19 | 2024-06-28
 10 | Hawks   |     26 | 2024-05-28

Notice that the resulting table contains a new column named random_date that contains a random date between 2024-01-01 and 2024-12-31.

Note: We use the syntax ::date to cast the result to a date. If you would rather generate a random timestamp between two dates, simply leave this out.

Feel free to use similar syntax to generate random dates in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Generate a Random Integer
PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Count Occurrences of Each Value in Column