Search code examples
sqlpostgresqlfunctiondatetimeutility

How to create a composite datetime with fixed date + random time interval in Postgresql

I'm new to Postgresql, and I'm looking for a way to return a composite datetime, built with a fixed date, and a random time, defined as an interval.

An example:

2020-12-02 10:00:00
2020-12-02 10:10:20
2020-12-02 08:25:23
2020-12-02 09:12:11

As you can see the date is fixed, and time remains between 08:00:00 and 10:30:00.

Do you know how to replicate this behaviour using a Postgresql query?

Solution

  • I would suggest:

    select '2020-12-02 08:00:00'::timestamp + random() * interval '2 hour 30 minute'

    Or, for the current date, you could express this as:

    select current_date + interval '08:00:00' + random() * interval '02:30'

    Note that these illustrate two different ways of expressing an interval with 2 hours and 30 minutes (and not even giving interval '150 minute' as an example).