GBase 8a: DateDiff and TimestampDiff Have Opposite Parameter Orders

Both DATEDIFF and TIMESTAMPDIFF in GBase 8a calculate the difference between two dates, but their parameter order is reversed. This can easily lead to wrong results if you're not careful. PERIOD_DIFF follows a similar pattern. Let's clarify the differences with examples in a gbase database.

The Key Difference

• DATEDIFF: Computes date1 - date2 in days. If date1 is later than date2, the result is positive.
• TIMESTAMPDIFF: Computes date2 - date1 and allows you to specify the unit (year, month, day, etc.). If date1 is later than date2, the result is negative.
• PERIOD_DIFF: Computes date1 - date2 in months. Arguments are integers in YYYYMM or YYMM format.

Examples

-- DATEDIFF: now() - '2004-05-01'
gbase> SELECT DATEDIFF(NOW(), '2004-05-01');
+------------------------------+
| DATEDIFF(NOW(), '2004-05-01') |
+------------------------------+
|                         6977 |
+------------------------------+

-- TIMESTAMPDIFF: '2004-05-01' - now(), returns the opposite sign
gbase> SELECT TIMESTAMPDIFF(DAY, NOW(), '2004-05-01');
+---------------------------------------+
| TIMESTAMPDIFF(DAY, NOW(), '2004-05-01') |
+---------------------------------------+
|                                 -6977 |
+---------------------------------------+

-- PERIOD_DIFF: 20230608 - 20230607
gbase> SELECT PERIOD_DIFF(20230608, 20230607);
+--------------------------------+
| PERIOD_DIFF(20230608, 20230607) |
+--------------------------------+
|                              1 |
+--------------------------------+

With the same pair of dates, DATEDIFF returns 6977 while TIMESTAMPDIFF returns -6977 — exactly the opposite sign. The reason is simply the reversed order of operands.

How to Remember

• If you prefer "recent date minus old date" giving a positive number, use DATEDIFF.
• When using TIMESTAMPDIFF, keep in mind it's "second argument minus first argument".
• PERIOD_DIFF follows the same order as DATEDIFF.

Always double‑check which function you're using and the position of the arguments to avoid sign errors in your time‑based calculations. This small detail can prevent significant data accuracy issues in your GBASE analytical queries.