Firebird 2.1 TIMESTAMP arithmetic and civil intervals

views:

372

answers:

+2 Q:

Firebird 2.1 TIMESTAMP arithmetic and civil intervals

My understanding is that, in keeping with Interbase v6, Firebird 2.5 does not support the SQL-92 INTERVAL keyword. At least, so suggests this reference and my repeated SQLCODE -104 errors trying to get INTERVALs to work under Firebird's isql(1).

How, then, do I account for the irregularities in our civil reckoning of time -- months aren't uniformly long, nor are days with savings time and leap adjustments, not to mention the year of confusion, etc. -- when performing TIMESTAMP arithmetic under Firebird 2.1?

How can I easily determine "one month earlier" or "one week later" than a given TIMESTAMP? How about "one day later" or "two hours before"?

+1 A:

If this isn't implemented in Firebird, maybe you could do it through the technology calling your sql server, and get the resulting timestamp ? I know it can be quite easily done in Java or .Net

Valentin Rocher 2010-01-22 20:15:47

That's certainly a feasible alternative, Bishiboosh, but I'd rather work out the right SQL or server-side logic once, than several times with a variety of clients if at all possible.

pilcrow 2010-01-22 21:15:02

+2 A:

You may use the DateAdd() and DateDiff() built-in functions.
Alternatively you may also use classic date arithmetic.

Douglas Tosi 2010-01-22 21:28:25

"Classic" date arith. doesn't let one work with civil intervals as an operand, but `DATEADD` and `DATEDIFF` seem spot on. (I can't convince either method, however, to recognize the savings time change that happened in my time zone on 2009-03-08 -- too bad.)

pilcrow 2010-01-22 22:52:19

@pilcrow: You can't get it to recognize DST changes, as that would assume the date / time to be local. The functions as-is seem to work with system time, which is a good thing as a database should always store timestamps in UTC.

mghie 2010-01-23 05:55:57

@mghie, my system and my Firebird server are in local time.

pilcrow 2010-01-23 22:53:45

@pilcrow: I guess most systems are, but that doesn't matter. The moment a database grows in usage to different time zones one wishes to have used UTC for all time stamps. The other possibility would be to store the offset to UTC with each time stamp, which still wouldn't allow for proper arithmetic, and there's no data type for it either. So UTC seems to be the only forward compatible way to store time stamps.

mghie 2010-01-24 09:10:16

+1 A:

You can also use the functions of the external UDF fbudf.dll

Hugues Van Landeghem 2010-01-23 13:36:07

ansaurus

tags:

views:

answers:

Firebird 2.1 TIMESTAMP arithmetic and civil intervals

related questions