Re: [PATCH] Generate random dates/times in a specified range

Le 10.07.2025 00:14, Tom Lane a écrit :
> Damien Clochard <damien@dalibo.info> writes:
>> So this adds 5 new variants of the random() function:
>
>>      random(min date, max date) returns date
>>      random(min time, max time) returns time
>>      random(min time, max time, zone text) returns timetz
>>      random(min timestamp, max timestamp) returns timestamp
>>      random(min timestamptz, max timestamptz) returns timestamptz
>
> I'm a little uncomfortable with this proposal, mainly because it
> overloads the random() function name to the point where I'm afraid
> of "ambiguous function" failures in SQL code that used to be fine.
>

Hi

Thanks for the feedback !

I agree with this, I overloaded the random() function because this is
what was done previously with `random(int,int)` and I did the same like
the good sheep that I am :)
but i'm fine with renaming this functions to daterandom, timerandom or
whatever....

> The traditional way of achieving these results would be something like
>
>     select now() + random() * interval '10 days';
>
> and I'm not convinced that the use-case is so large as to justify
> adding built-in forms of that.
>


 From my experience, when users are writing a set of masking rules, they
tend to anonymize the dates with "a random date between start_date and
end_date"

Which can be trasnlated like this

SELECT start_date+(random()*(end_date-start_date))::interval;

But when you have hundreds of masking rules, the meaning of this one is
not so clear.


Now with PostgreSQL 18, we can write

SELECT random(start_date::int, end_date::int)::date;


Which is more explicit, but we could extend that logic to:

SELECT daterandom(start_date,end_date);


I agree this is merely syntactic sugar for the developers, but I don't
see why it is ok to provide random(int,int) or random(numeric,numeric)
and why random(date,date) is not.

Regards,

--
Damien Clochard