System date and time into a stored procedure

Sasa Mihajlovic wrote:

I would like to write a Firebird SP that will insert system date and time into 2 fields of changed record into some table. What is syntax for this SP?

Helen Borrie answers:

You use context variables:

CURRENT_DATE
CURRENT_TIME

and the date literal 'NOW'

CURRENT_TIME will return the same time for all records affected by a single execution of your statement.

If you have multiple records affected by a single statement execution and you need to have the exact times you can use a complicated EXTRACT expression operating on (CAST ('NOW' AS TIMESTAMP)) to get HOURs, MINUTEs and SECONDs of time. From Fb 2 onward you can also get MILLISECOND, as follows:

AS
...
  declare TimeVar TIME;
  declare TimeNow TIMESTAMP;
...
BEGIN
  ...
  TimeNow = CAST ('NOW' AS TIMESTAMP);
  TimeVar = CAST ( ('' ||
          EXTRACT (HOUR  from TimeNow) || ':' ||
          EXTRACT (MINUTE  from TimeNow) || ':' ||
          EXTRACT (SECOND  from TimeNow) || '.' ||
          EXTRACT (MILLISECOND  from TimeNow)  )
          as TIME);

It would be so much easier to store just one field, a timestamp, which could be CURRENT_TIMESTAMP or CAST('NOW' AS TIMESTAMP), according to requirements...