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
as
BEGIN
if (current_timestamp > cast(‘2007-11-03’ as timestamp)) then
exception myerror;

END

It should be noted that CURRENT_TIMESTAMP returns the timestamp of transaction start, which means it is constant during the transaction. If you need the exact timestamp of that very moment, use a special value ‘now’. It’s a char(3) string, so you need to cast it to timestamp explicitly.


select cast(‘now’ as timestamp), cast(‘today’ as date)
from rdb$database


As you can see, the same rule applies for CURRENT_DATE, and you can use a special value ‘today’, to get the current value.

The reason behind all this is that transactions should be atomic, so CURRENT_DATE and CURRENT_TIMESTAMP provide consistency. Especially in 24/7 systems it can easily happen that the date changes between transaction start and end.

link: http://www.firebirdfaq.org/faq114/

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: