On Sat, Mar 19, 2011 at 7:11 AM, karolbieniaszewski <
liviuslivius@...> wrote:

>
>
>
> --- In firebird-support@yahoogroups.com, Mike Pomraning <mjp@...> wrote:
> >
> > Is there a technique to make the return value of a UDF constant across
> > the execution of a single query?
> >
> > For example, I'd like getExactTimestamp() -- or, rather, a UDF very
> > much like it -- to return the same result every time it is evaluated,
> > much like the context variable CURRENT_TIMESTAMP does.
> > [[SNIP]]
>
> Why not use CURRENT_TIMESTAMP?
> An why you need do something so wrong like fb_sleep in query?
> What is your real interest?
>

I'd like to perform date/time arithmetic in UTC in some queries, without
assuming that the server's time zone is UTC. (fb_sleep() was written merely
to exaggerate the delays to show obviously that the UDF is evaluated once
per row, rather than just once per query.)

If I understand correctly, CURRENT_TIMESTAMP unfortunately cannot be
guaranteed to be in UTC. getExactTimestampUTC(), unfortunately, is called
repeatedly, meaning that something like this:

SELECT getExactTimestampUTC() - record_insertion_timestamp_utc AS
how_recent, ...

will show a marked skew if the query takes too long (e.g., system is heavily
loaded, or there are millions of rows).

-Mike


[Non-text portions of this message have been removed]