How to get the Firebird server date and time?

There are two variables available, CURRENT_DATE which returns the current date and CURRENT_TIMESTAMP which returns date and time. You can use those in SQL statements:

insert into t1 values(10, CURRENT_TIMESTAMP);

To query the current time, use this:

select current_date, current_timestamp
from rdb$database;

You need to select from something and rdb$database is a convenient one-row table that is present in each database.

You can also assign those variables in PSQL (stored procedure and trigger language):

create procedure p1
if (current_timestamp > cast('2007-11-03' as timestamp)) then
exception myerror;

It should be noted that CURRENT_TIMESTAMP returns the timestamp of statement start, which means it is constant during the statement execution. If you need the exact timestamp of that very moment, use a special value 'now'. If you want to select it, you need to cast it to timestamp explicitly. Without CAST you would get char(3) string 'now' instead. If you assign it to a timestamp variable or compare with some column, the required datatype is known so there is no need for casting.

select cast('now' as timestamp), cast('today' as date)
from rdb$database;

set term ^ ;
execute block returns (ts timestamp, dt date) as
ts = 'now';
if (ts = 'now') then dt = 'today';


As you can see, the same rule applies for CURRENT_DATE, and you can use a special value 'today', to get the current value. Beside that, you might also find 'tomorrow' and 'yesterday' useful.

The reason behind all this is that statements should be atomic, so CURRENT_DATE and CURRENT_TIMESTAMP provide consistency. If statement execution takes a long time it can easily happen that the date changes between statement start and end.

Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

All contents are copyright © 2007-2023 unless otherwise stated in the text.

Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

 Installation and setup
 Backup and restore
 Connectivity and API
 Errors and error codes