Обсуждение: [PATCH] Generate random dates/times in a specified range

Hello,

As I am involved in the PostgreSQL Anonymizer extension, I found that 
the random(min,max) functions introduced recently are very useful to 
generate synthetic data or define a masking policy.

I decided to submit a similar set of functions for random dates and 
times.

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

Each one returns a random date/time value t in the range min <= t <= 
max.

For the timetz function, a third parameter is required to define the 
timezone.
However if the value is an empty string, the session timezone is used.

These functions all rely on the pg_prng_int64_range function developped 
in
PG 17 for the random(bigint,bigint) function.

Regards,

-- 
Damien Clochard

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.

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.

            regards, tom lane

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

On Sat, 12 Jul 2025 at 16:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
>
> I like the idea, especially the date variant. Unlike Tom, I'm not particularly concerned about breakage of existing
scripts,as
 
> most already are working just fine with raw numbers and I don't see this patch breaking them.
>
> In a selfish vein, I would use the "date" and timestamp variants a lot. I would use the "time" ones seldom to never.

But it's completely trivial to emulate random(min_date, max_date), just by doing

  min_date + random(0, max_date - min_date)

Is it really worth adding a core function for that?

Regards,
Dean

On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On Sat, 12 Jul 2025 at 16:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> >
> > I like the idea, especially the date variant. Unlike Tom, I'm not particularly concerned about breakage of existing
scripts,as 
> > most already are working just fine with raw numbers and I don't see this patch breaking them.
> >
> > In a selfish vein, I would use the "date" and timestamp variants a lot. I would use the "time" ones seldom to
never.
>
> But it's completely trivial to emulate random(min_date, max_date), just by doing
>
>   min_date + random(0, max_date - min_date)
>
> Is it really worth adding a core function for that?
>

I feel like this is a very similar argument against what was
ultimately the addition of timestamp based generate_series functions,
and similarly I think adding these in would be a rather useful
improvement for users, though like generate_series, we don't need to
hit every different data type (no one should ever generate a random
timetz for instance).


Robert Treat
https://xzilla.net

On Tue, 15 Jul 2025 at 04:49, Robert Treat <rob@xzilla.net> wrote:
>
> On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> >
> > But it's completely trivial to emulate random(min_date, max_date), just by doing
> >
> >   min_date + random(0, max_date - min_date)
> >
> > Is it really worth adding a core function for that?
>
> I feel like this is a very similar argument against what was
> ultimately the addition of timestamp based generate_series functions,
> and similarly I think adding these in would be a rather useful
> improvement for users, though like generate_series, we don't need to
> hit every different data type (no one should ever generate a random
> timetz for instance).

Right, and for generate_series() we didn't add a variant for type
date. In fact, we explicitly documented how a sequence of dates can be
generated using the date-plus-integer operator.

In fact, I think generate_series() sets a good precedent, and I could
get behind a proposal that adds new random() functions with the same
signatures as the timestamp-based generate_series() functions. In
particular, I think that it's quite likely that if I wanted a random
timestamp in some range, I would want some specified precision, like
say 'hour' or 'day', and not a timestamp with some random number of
microseconds.

(In a similar vein, it might be useful to add an optional "step"
parameter to the random integer/numeric functions, but that's really a
separate proposal.)

Regards,
Dean

Greg Sabino Mullane <htamfids@gmail.com> writes:
> On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
>> Is it really worth adding a core function for that?

> Yes, I think it is. It is also trivial to get a random int from 50 to 100
> with
> 50 + floor(random() * 51)
> but random(50,100) is so much nicer.

I won't object to adding this for date/timestamp/timestamptz, but
I don't really believe that the time/timetz variants are worth the
code space.  I think the latter two would also have definitional
issues because of the modulo behavior of those types' underlying
arithmetic.

            regards, tom lane

Le 21.07.2025 21:06, Greg Sabino Mullane a écrit :
> Damien, maybe we can let the time ones go? Tom and I are not big fans
> of those, and nobody else has stepped up to defend them.
>

Sure !

Here's a second version with the following changes

- remove time and timetz variants
- disallow infinity boundaries
- remove unnecessary int32 cast
- add examples in the documentation
- run pgindent
- update tests
- fix typos

Thanks for the feedback and insights

--
Damien Clochard

On 24/07/2025 17:20, Damien Clochard wrote:
> Le 21.07.2025 21:06, Greg Sabino Mullane a écrit :
>> Damien, maybe we can let the time ones go? Tom and I are not big fans
>> of those, and nobody else has stepped up to defend them.
>>
>
> Here's a second version with the following changes
>
> - update tests 


Just a small cosmetic gripe, the regression test comments are usually 
"-- ok" and "--fail", and not "--  Should error".


No comment on the rest of the patch.

-- 

Vik Fearing

Le 24.07.2025 20:24, Vik Fearing a écrit :
>> [...]
> 
> Just a small cosmetic gripe, the regression test comments are usually
> "-- ok" and "--fail", and not "--  Should error".
> 

Thanks Vik!

Here's a third version with the following changes:

* Rebase from master
* Replace 'Should error' by 'fail' in the test file
* move documentation from func.sgml to func/func-datetime.sgml

Note that former random function variants are documented in 
func/func-math.sgml but it felt more logical to document the new 
variants in func-datetime.sgml. As a result the random variants would be 
documented in 2 separate sections of chapter 9.

-- 
Damien Clochard

Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
> Your v3 did not get attached to the previous email.

My bad, here it is

--
Damien Clochard

On Mon, 25 Aug 2025 at 12:42, Damien Clochard <damien@dalibo.info> wrote:
>
> Note that former random function variants are documented in
> func/func-math.sgml but it felt more logical to document the new
> variants in func-datetime.sgml. As a result the random variants would be
> documented in 2 separate sections of chapter 9.

Note the paragraph below the table listing the current random
functions, explaining the PRNG and the interaction with setseed(). If
you document the new functions in a separate section, that text would
need updating to refer to 2 separate locations (and possibly more in
the future), which seems a little messy. So I think it would be better
to keep documenting all the random functions together in the same
table.

Regards,
Dean

On Aug 26, 2025, at 22:17, Damien Clochard <damien@dalibo.info> wrote:

Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :

Your v3 did not get attached to the previous email.

My bad, here it is

--
Damien Clochard<v3-0001-Generate-random-dates-times-in-a-specified-range.patch>

Code change looks good to me. Maybe we can make check_range_boundaries() “inline” as well.

But a major problem is, I think we should bump CATALOG_VERSION_NO. Otherwise, running your code with an existing database, the new functions won’t work.

Traditionally, the patch committer bumps the catversion, not the patch author.

-- 

Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:
> On 29/08/2025 11:12, Chao Li wrote:
>> But a major problem is, I think we should bump CATALOG_VERSION_NO. 

> Traditionally, the patch committer bumps the catversion, not the patch 
> author.

Yeah.  If you include a catversion bump in a submitted patch, you can
expect the patch to break repeatedly while it's sitting in the queue,
due to unrelated patches changing catversion.  So we prefer to have
the committer add that at the last moment.

If you're worried that the committer might forget that, you can
add a comment about it in the patch's draft commit message.

            regards, tom lane

On Tue, 26 Aug 2025 at 15:17, Damien Clochard <damien@dalibo.info> wrote:
>
> Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
> > Your v3 did not get attached to the previous email.
>
> My bad, here it is

It seems like we have reached a consensus on adding just the random
date and timestamp[tz] functions, so I took a more detailed look with
an aim to committing this.

I'm attaching v4 with a few minor updates:

1). Updated the paragraph of text below "Table 9.6. Random Functions"
to also refer to "Table 9.33. Date/Time Functions", so that it's clear
that all the comments that follow apply to the date/time random()
functions too -- in particular, the part about setseed(). Having
reflected on it, I think that's a slightly better option than putting
the new functions in Table 9.6, because that's part of a whole section
about mathematical functions and everything there refers to number
types, not dates/timestamps, so putting the new functions there feels
a little out-of-place.

2). Changed check_range_boundaries() to a macro CHECK_RANGE_BOUNDS().
This feels a little neater, since it's such a trivial check, and the
datatype is not always int64.

3). Changed the C function names, adding an underscore for better
readability and consistency with other date/timestamp functions.

4). Used the DATE/TIMESTAMP_IS_NOBEGIN/NOEND() macros for neatness.

5). I didn't like this error message:

ERROR:  lower and upper bound cannot be infinite

because it's not grammatically correct, so I changed it to this:

ERROR:  lower and upper bounds must be finite

which is an error already used elsewhere for similar checks. This is
not quite the same as the errors thrown by random_numeric() -- perhaps
that should be changed to match (making its errors the same as the
errors thrown by width_bucket_numeric()).

6). It's not necessary to include utils/builtins.h or utils/datetime.h.

I think this is now committable, so if there are no objections, I'll
push this shortly.

Regards,
Dean

On Mon, 8 Sept 2025 at 13:40, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> I think this is now committable, so if there are no objections, I'll
> push this shortly.

Committed.

Regards,
Dean

On Wed, 10 Sept 2025 at 14:30, Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> This patch adds functions func-datetime.sgml but those functions depend on setseed function, which is not there.
> Wouldn't it be good to add a note that setseed() will change the result of those random functions ?

Agreed. I think something like the attached ought to be sufficient.

Regards,
Dean

Le 09.09.2025 12:00, Dean Rasheed a écrit :
> On Mon, 8 Sept 2025 at 13:40, Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
>>
>> I think this is now committable, so if there are no objections, I'll
>> push this shortly.
>
> Committed.
>

Thanks a lot for your help on this !

--
Damien Clochard