SQLAlchemy: Difference between two dates

Question

I'd like to get the difference between two dates working on SQLAlchemy. Using Postgresql I have the following working:

SELECT
    EXTRACT(EPOCH FROM ('2019-02-11 17:59:05.953894'::timestamp - '2019-02-11 17:59:01.953894'::timestamp))

However, I have problems when attempting the same in SQLAlchemy:

session.query(func.extract('epoch',func.date(subquery.c.dt_final.cast(Date)))-
                                   func.date(subquery.c.dt_start.cast(Date))).all()

Getting this error:

ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: double precision - date
LINE 1: ...h FROM date(CAST(anon_2.dt_final AS DATE))) - date(CAS...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

How is the proper way to get the difference between two dates in SQLAlchemy?

Thanks

Notice that in the SQLA version you extract, then subtract. In the raw SQL you have it the other way around. — Ilja Everilä, Mar 03 '20 at 21:36
Also, since in your SQLA version you cast as date, the result of the subtraction would be the number of days between the 2 dates. Did you mean to cast as timestamps instead? — Ilja Everilä, Mar 03 '20 at 22:04

score 4 · Answer 1 · answered Mar 03 '20 at 21:51

You can use datediff for date diferences:

SELECT  DATEDIFF(Here_you_put_units, '2019-02-11 17:59:05.953894', '2019-02-11 17:59:01.953894');

units you can use:

year    yy, yyyy
quarter qq, q
month   mm, m
dayofyear   dy, y
day dd, d
week    wk, ww
hour    hh
minute  mi, n
second  ss, s
millisecond ms
microsecond mcs
nanosecond  ns

Save as a var:

 SELECT  DATEDIFF(Here_you_put_units, '2019-02-11 17:59:05.953894', '2019-02-11 17:59:01.953894') AS needed_date;

SQL_alchemy:

from sqlalchemy import func, text
from datetime import datetime
date1 = datetime.fromisoformat('2019-02-11 17:59:05.953894')
date2 = datetime.fromisoformat('2019-02-11 17:59:01.953894')
func.datediff(text('Here_you_put_units'), date1, date2)

Thanks dreeeeeedd, I found that this is not possible: https://stackoverflow.com/questions/42485952/calculate-datediff-in-postgres-using-sqlalchemy — John Barton, Mar 03 '20 at 22:38

score 0 · Answer 2 · answered Mar 03 '20 at 21:34

0

You may need to switch from timestamps to datetime, but there's a built-in function called timediff() which works similarly to things like count() and max(). I'm not familiar with SQLalchemy but it should be pretty universal.

answered Mar 03 '20 at 21:34

Boud225

23
7

score 0 · Answer 3 · answered Sep 19 '21 at 07:36

The SQL statement can be reproduced with this code:

q1 = sa.select(func.extract('epoch',
                            subquery.c.dt_final.cast(TIMESTAMP) -
                            subquery.c.dt_start.cast(TIMESTAMP)))

where dt_start and dt_final are String columns containing the timestamp strings in the question. The result column is a float (4.0,)

However in Postgresql we can subtract the timestamps directly

test# SELECT 
    '2019-02-11 17:59:05.953894'::timestamp - '2019-02-11 17:59:01.953894'::timestamp AS result;
  result  
══════════
 @ 4 secs
(1 row)

so we can do the same in SQLAlchemy:

q2 = sa.select(subquery.c.dt_final.cast(TIMESTAMP) - subquery.c.dt_start.cast(TIMESTAMP))

which returns a datetime.timedelta: (datetime.timedelta(seconds=4),)

If the goal is to subtract dates rather timestamps, cast the timestamp strings to Date and subtract

q3 = sa.select(subquery.c.dt_final.cast(Date) - subquery.c.dt_start.cast(Date))

which will return an integer column, for example (10,).

SQLAlchemy: Difference between two dates

3 Answers3