Date and Time Calculations
When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where
* the integral fraction is the number of days
* the decimal fraction is the part of a day
For example:
* Two days: 2.0
* One hour: 1.0/24.0
* One minute: 1.0/1440.0
* One second: 1.0/86400.0
Extracting the smaller units from a TIMESTAMP value:
* Number of seconds: VALUE*86400.0
* Number of minutes: VALUE*1440.0
* Number of hours: VALUE*24.0
Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.
Posted by Dan : 2:04 PM
---------------
firebird:
tgl jam server sekarang : current_timestamp
tgl : CAST ('today' AS TIMESTAMP)
jam : extract(hour from current_timestamp)||':'||extract(minute from current_timestamp)||':'||extract(second from current_timestamp)
Saturday, February 24, 2007
Date and Time Calculations (Firebird)
Subscribe to:
Post Comments (Atom)
1 comment:
Good to know. Until now, I always converted timestamps to integers to use them in calculations.
This way, using decimals is much easier, however note that firebird uses the precision of the used decimal: if you deduct 2 hours of the current time using 2.0/24.0, you will end up with a time approximately 1h55min ago!
If you use 2.00000/24, this rounding problem is overcome.
SQL statements:
select timestamp 'now' - 2.0/24.0 from rdb$database
=> result will be wrong: 1h55min ago,
select timestamp 'now' - 2.00000/24.0 from rdb$database
=> result will be right: 2h ago!
Regards
Stijn
Post a Comment