Today we will see a SQL tip, very useful in our day to day.

There are cases that we are setting up our query, procedure or trigger and we need to change the value of a timestmp field, more precisely to add or remove hours.

the first thing that comes to mind, would be something like this

- to remove an hour, we normally think so, but that removes a day. 
SELECT 
CURRENT_TIMESTAMP  -  1 
FROM rdb $ database r

As stated in the comment, the sql above removes one day.

Then, using logic, we can reach the following conclusion:

-- add 12 hours.
SELECT
CURRENT_TIMESTAMP + 0.5
FROM rdb$database r
 
-- remove 6 hours.
SELECT
CURRENT_TIMESTAMP - 0.25
FROM rdb$database r
 
-- add 3 hours.
SELECT
CURRENT_TIMESTAMP + 0.125
FROM rdb$database r
 
--...

It even works, trying to calculate to find the corresponding value
1 – 24 hours
0.5 – 12 hours
0.25 – 6 hours
…

However, it is easy to make mistakes and it is difficult to read for someone else who will read the commands.

So, searching, I found the following UDF that comes as a standard when installing firebird.
ADDHOUR

As its name suggests, with it we can easily include hours and we will even use it to remove hours. Let’s take the examples:

To use it in general it is addhour (value_time_stamp, hours).

-- add 3 horus.
SELECT
addhour( CURRENT_TIMESTAMP, 3 )
FROM rdb$database r
 
- removing one hour 
SELECT 
addhour (  CURRENT_TIMESTAMP ,  - 1  ) 
FROM rdb $ database r

The secret to reducing hours was to pass the negative parameter.

There are other UDFs native to the installation that serve to add and remove minutes, seconds, days, etc. But the approach will be for a next post.

SQL queries were tested on Firebird 3.0.

They hope you enjoyed it guys.

Questions or suggestions? Leave your comment!

Good codes and see you next time. Thanks!