*I was curious about how ***ChatGPT*** can generate code so I tried it out on PL/pgSQL for ***PostgreSQL ***and gave it a simple task of creating a function for event scheduling. Here's what we got after a few iterations.*

**Me**: generate a *plpgsql* function, which takes an *event ID*, a *past timestamp*, the *current timestamp*, the *repeating interval* and calculates when the event specified by its *ID* becomes current (without time zone)!**Raw solution suggested by ChatGPT:**`CREATE OR REPLACE FUNCTION get_current_event(event_id integer, past_timestamp timestamp without time zone, current_timestamp timestamp without time zone, repeating_interval interval)`

`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

`BEGIN`

` result := past_timestamp + repeating_interval * floor((current_timestamp - past_timestamp) / repeating_interval);`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function takes in an `event_id`

, `past_timestamp`

, `current_timestamp`

, and `repeating_interval`

as input and calculates the next occurrence of the event based on the repeating interval. The calculation is performed by subtracting the `past_timestamp`

from the `current_timestamp`

and dividing the result by the `repeating_interval`

, then taking the *floor* of the result. Finally, the `repeating_interval`

is multiplied with this result and added to the `past_timestamp`

to get the next occurrence of the event.**Me**: how do you want to apply `floor()`

function used for *floating point numbers* on an *interval* type?**ChatGPT**: I apologize for the mistake in my previous answer. The `floor`

function cannot be used directly on an `interval`

data type. A common way to achieve the desired result is to *cast* the `interval`

to a *numeric type* (such as `double precision`

) and then apply the `floor`

function, and finally cast the result back to an `interval`

. Here is an updated version of the function:`CREATE OR REPLACE FUNCTION get_current_event(event_id integer, past_timestamp timestamp without time zone, current_timestamp timestamp without time zone, repeating_interval interval)`

`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

` elapsed_time double precision;`

`BEGIN`

` elapsed_time := extract(epoch from (current_timestamp - past_timestamp)) / extract(epoch from repeating_interval);`

` elapsed_time := floor(elapsed_time);`

` result := past_timestamp + interval '1 second' * extract(epoch from repeating_interval) * elapsed_time;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

**Me**: I already tried `epoch`

on *fixed intervals* (such as `30 days`

) but after a while the *timestamp* of the next occurence of the event started to be late.**ChatGPT**: if you're experiencing drift in your results when using the `extract`

function, it's likely due to the fact that some intervals are not exactly representable as a *fixed number* of seconds. Instead, you can use the `date_trunc`

function to round down to the nearest repeating interval. Here's an updated version of the function:`CREATE OR REPLACE FUNCTION get_current_event(event_id integer, past_timestamp timestamp without time zone, current_timestamp timestamp without time zone, repeating_interval interval)`

`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

`BEGIN`

` result := date_trunc(text(repeating_interval), past_timestamp + (current_timestamp - past_timestamp));`

` WHILE result <= past_timestamp LOOP`

` result := result + repeating_interval;`

` END LOOP;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function uses the `date_trunc`

function to round down to the *nearest repeating interval*, and then repeatedly adds the interval until the result is after the `past_timestamp`

. This approach should result in more accurate timestamps for repeating events.**Me**: is there any way to eliminate the `WHILE ... LOOP`

using mathematical *modulo* function only?**ChatGPT**: yes, it is possible to eliminate the `WHILE ... LOOP`

by using the *modulo* operator (`%`

). Here is an updated version of the function:`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

`BEGIN`

` result := past_timestamp + ((current_timestamp - past_timestamp) / repeating_interval) * repeating_interval;`

` IF result < past_timestamp THEN`

` result := result + repeating_interval;`

` END IF;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function uses the *modulo operator* (`/`

) to calculate the number of full intervals that have elapsed between `past_timestamp`

and `current_timestamp`

, and then multiplies that value by `repeating_interval`

to get the *next occurrence* of the event. If the result is still before `past_timestamp`

, an additional `repeating_interval`

is added to ensure that the next occurrence is in the future.**Me**: are you sure you can multiply by `interval`

and the *modulo operator* isn't `%`

?**ChatGPT**: I apologize for the mistake. You are correct, the modulo operator is `%`

in *PL/pgSQL*, not `/`

. Here is the corrected version of the function:`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

` num_intervals double precision;`

`BEGIN`

` num_intervals := (current_timestamp - past_timestamp) / repeating_interval;`

` num_intervals := floor(num_intervals);`

` result := past_timestamp + num_intervals * repeating_interval;`

` IF result < current_timestamp THEN`

` result := result + repeating_interval;`

` END IF;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function uses the `/`

operator to calculate the number of *full intervals* that have elapsed between `past_timestamp`

and `current_timestamp`

. The `floor`

function is then used to round down to the *nearest whole number of intervals*, which is multiplied by `repeating_interval`

to get the *next occurrence* of the event. If the result is still before `current_timestamp`

, an additional `repeating_interval`

is added to ensure that the next occurrence is in the future.**Me**: are you sure you can *divide* an `interval`

by another `interval`

?**ChatGPT**: I apologize for the mistake. Dividing an `interval`

by another `interval`

is not supported in *PostgreSQL*. Instead, you can divide the *interval* by a *numeric value* to get a new interval value. Here is the corrected version of the function:`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

` elapsed_time interval;`

` num_intervals double precision;`

`BEGIN`

` elapsed_time := current_timestamp - past_timestamp;`

` num_intervals := elapsed_time / repeating_interval;`

` num_intervals := floor(num_intervals);`

` result := past_timestamp + num_intervals * repeating_interval;`

` IF result < current_timestamp THEN`

` result := result + repeating_interval;`

` END IF;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function calculates the ... (?)**Me**: you are still dividing `elapsed_time`

(`interval`

) by `repeating_interval`

(`interval`

).**ChatGPT**: I apologize for the mistake. You are correct, dividing an `interval`

by an `interval`

is not possible. Here is a corrected version of the function, which uses a *constant* to divide the `interval`

:`RETURNS timestamp without time zone AS $$`

`DECLARE`

` result timestamp without time zone;`

` elapsed_time interval;`

` num_intervals double precision;`

` interval_length double precision;`

`BEGIN`

` elapsed_time := current_timestamp - past_timestamp;`

` interval_length := extract(epoch from repeating_interval);`

` num_intervals := extract(epoch from elapsed_time) / interval_length;`

` num_intervals := floor(num_intervals);`

` result := past_timestamp + (num_intervals * repeating_interval);`

` IF result < current_timestamp THEN`

` result := result + repeating_interval;`

` END IF;`

` RETURN result;`

`END;`

`$$ LANGUAGE plpgsql;`

This function uses the `extract`

function to convert the `repeating_interval`

and `elapsed_time`

`interval`

s to their respective `epoch`

values in *seconds*, and then divides `elapsed_time`

by `interval_length`

to calculate the number of *full intervals* that have elapsed. The `floor`

function is then used to round down to the nearest *whole number of intervals*, which is multiplied by `repeating_interval`

to get the next occurrence of the event. If the result is still before `current_timestamp`

, an additional `repeating_interval`

is added to ensure that the next occurrence is in the future.**Me**: looks good!**ChatGPT**: I'm glad it looks good. If you have any further questions or if there's anything else I can help with, please don't hesitate to ask.

This is how far we got yet. With only minor modifications (we don't actually need `event_id`

parameter for instance as we care only about the *timestamp*) I could make this function work on a *PostgreSQL* database server instance so I can say ChatGPT comes handy when you have to write a smaller block of code from stratch but not too large (as there will be errors in the code but the idea behind it will probably work and suit your needs). I'm sure the service will get many improvements in the future, this was just a quick test and I'm impressed! It's pretty good!

You can give *ChatGPT* a try on the following website (and it really worth it): https://chat.openai.com/

*(All links are valid at the time of writing.)*

## Comments