TimestampDiff

[krisi1998]

Hello,

first, thanks for this package @tpetry, I'm recently using this package in my project and looks great so far.
However I had to implement a TimestampDiff function for my use case, and was wondering if you would
be interested in having a look.

Would you be interested having such a function part of the package?
The one thing I'm confused is how to actually run and test the generated SQL per driver
and verify the result as I don't have much experience with PEST.

[tpetry]

What will a TimestampDiff do? Provide it two timestamps and it calculates the seconds between them?

[krisi1998]

It is based on mysql timestampdiff https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_timestampdiff
basically provide two timestamps and get the diff in the specified unit.

[tpetry]

What are the units you would want to support? Is this implementable on all databases? My first guess was to use unix timestamps but this would introduce inaccuracy with leap seconds.

[krisi1998]

Not sure what you had in mind with unix timestamps, but from what I see about different databases:

MySQL -> use timestampdiff directly

SQL server -> use similar datediff
https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver17

PostgreSql -> combination of extract and epoch / extract and age

SELECT EXTRACT(YEAR FROM AGE('2025-11-22'::date, '2026-11-22'::date)) AS diff;

SELECT EXTRACT(EPOCH FROM ('2025-11-22'::timestamp - '2026-11-22'::timestamp)) / (60 * 60 * 24) AS diff;

SQLite -> more research needed, but should be possible

Supported units would be seconds, minutes, hours, days, weeks, months and years.

I don't think any of these functions account for leap seconds, even internal MySQL and SQL server functions, and personally I don't need that level of accuracy. Only that I can avoid writing driver specific SQL and the results are consistent across databases.

as an example: https://github.com/krisi1998/laravel-query-expressions/blob/feat/timestampdiff/src/Function/Time/TimestampDiff.php

[tpetry]

I am open for this expression. Feel free to work on it. But the implementation must follow these rules:

• Returns integers (so only full units between both dates)
• $from and $to are required parameters
• $unit should be a string (i try to make the package simple and don't want to add too much stuff like enums if not really needed)
• no manual artithmetic is used

The last one is important. Your example will fail for PostgreSQL with dividing by 60 * 60 * 24 works for the days unit. but as soon as you have months or years its not working anymore because of leap years. I guess your month's workaround for PostgreSQL may therefore also fail if the dates are chosen unfortunately? Let's start by not supporting the week unit to have a good first implementation.

[krisi1998]

Here are some comparisons on different DBs:

Setup:

-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  from_datetime TIMESTAMP default NULL, -- DATETIME on sqlserver
  to_datetime TIMESTAMP default NULL -- DATETIME on sqlserver
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, '2020-02-29 00:00:00', '2021-02-28 00:00:00');
INSERT INTO EMPLOYEE VALUES (0002, '2019-03-01 00:00:00', '2021-02-28 00:00:00');

MySql: https://onecompiler.com/mysql/445mbpqqs

SELECT empId, timestampdiff(month, from_datetime, to_datetime) as months, timestampdiff(day, from_datetime, to_datetime) as days FROM EMPLOYEE;

+-------+--------+------+
| empId | months | days |
+-------+--------+------+
|     1 |     11 |  365 |
|     2 |     23 |  730 |
+-------+--------+------+

Postgress: https://onecompiler.com/postgresql/445m8rjn3

SELECT empId, extract(YEAR from age(to_datetime, from_datetime)) * 12 + extract(MONTH from age(to_datetime, from_datetime)) as months, (extract(epoch from (to_datetime - from_datetime)) / (60 * 60 * 24))::int as days
FROM EMPLOYEE;

 empid | months | days 
-------+--------+------
     1 |     11 |  365
     2 |     23 |  730

SqlServer: https://onecompiler.com/sqlserver/445ma8vdv

SELECT empId, datediff(month, from_datetime, to_datetime) as months, datediff(day, from_datetime, to_datetime) as days FROM EMPLOYEE;

empId       months      days       
----------- ----------- -----------
          1          12         365
          2          23         730

interestingly datediff recognizes first case as a leap year and gives 12 months. It does not, it gives the same 12 months result for range '2020-02-29 00:00:00' -> '2021-02-01 00:00:00', ie. it does not check days at all, so maybe it should not be used.

Personally I think this is more of an issue on the usage than on the implementation. If your expected range is relatively small, (10^1, 10^2) and/or you require more accurate results for your use-case, you should maybe drop a unit or 2 and than perform your calculations.

Let me know if you are OK with these results and if each technique is usable.