Date time functions in rdb$database?

    Several times, I've been asked for some apparently undocumented date and time related functions that seems to apply to an enigmatic system table known as rdb$database and that give you the value of several pseudo variables. The last time the question appeared on IBO-list, I decided to assemble this brief explanation.

These aren't functions in rdb$database. Using this system table is a trick:

1.- SQL in Interbase needs ALWAYS a FROM clause. If you don't include this part, you will receive a syntax error from the server.

2.- If you ask for a constant or system value from another table, you'll get your answer as many times as records are in such table. For example, try,
select 'A' from rdb$relations
to understand what I mean. You will get a number of "A" equal to
select count(*) from rdb$relations
and the same for any other (user or system) table when you attempt the same.

3.- Rdb$database is a system table that holds a bit of information. It has only one record by definition. Don't alter it directly with SQL commands. It keeps the db-wide character set and the global security class (the second almost always NULL).

Hence, the only reason to use this table is that you get (guaranteed) your result only once. Examples:

In IB5 and earlier versions:

In IB6-dialect1:

In IB6-dialect3:

The apparent contradiction is because in IB5 and earlier versions as well as in IB6-dialect1, DATE is really date plus time, whereas in IB6-dialect3, DATE is date-only, TIME is time-only (not available previously) and TIMESTAMP is date plus time (not available previously). The change was done to give IB better compliance with the ANSI SQL specification.

In any IB version,

select gen_id(gen_name, 0) from rdb$database

will give you the current value of gen_name and

select gen_id(gen_name, N) from rdb$database

will give you the current value of gen_name after it was added N (N can be negative) in an atomic way. If you execute the second command against a table with R records, then it's the same than the example at the beginning with 'A': you'll end up calling the same command R times; as a result, you *probably* will change gen_name by N*R, but it's not guaranteed, because although gen_id is atomic, between calls to gen_id, other thread in the db may be scheduled that calls gen_id for the same generator.

The problem with the traditional functions is that they need a cast to be distinguishable from plain literal strings. To overcome that, IB6 invented three current_* variables as shown above. Fortunately, when passing parameters to stored procedures or when comparing against date fields, these constants doesn't need a cast nor need to be obtained from a select statement:

create table example (e int not null primary key,
    d date not null check(d >= 'today'));
create generator gen_ex;
commit;
insert into example (e, d) values (gen_id(gen_ex, 1), 'today');
insert into example (e, d) values (gen_id(gen_ex, 1), 'today');
insert into example (e, d) values (gen_id(gen_ex, 1), 'today');
update example set d = 'tomorrow';
commit;
select d - 'yesterday' from example;
commit;

should yield three rows with value 2 in the only output column.

Please, keep in mind that IB gives uses the same date and/or time value regardless of the times the date & time related functions are used. This is consistent with the rules almost any programming language uses. For example,

update example set d = 'today'

will use the same value for 'today' for all records, even if you update 9 million rows and you start at 23:50 and finish at 0:35.

This page was last updated on 2000-09-18 02:44:50