CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
JarochoEngineer
JarochoEngineer

Reputation: 1787

SQLAlchemy: Difference between two dates

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

Upvotes: 6

Views: 12938

Answers (3)

snakecharmerb
snakecharmerb

Reputation: 55799

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,).

Upvotes: 0

dreeeeeedd
dreeeeeedd

Reputation: 93

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)

Upvotes: 6

Boud225
Boud225

Reputation: 33

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.

Upvotes: 0

Related Questions