find timestamp difference between 2 columns with sqldf

Question

According to this answer: https://stackoverflow.com/a/25863597/12304000

We can use something like this in mysql to calculate the time diff between two cols:

SELECT TIMESTAMPDIFF(<INTERVAL>,<timestampFrom>,<timestampTo>);

How can I achieve the same thing with pandasql? I tried these:

from pandasql import sqldf
output = sqldf("select DATEDIFF(minute,startDate,completedDate) from df")
output = sqldf("select TIMESTAMPDIFF(MINUTE,startDate,completedDate) from df")

but they throw an error that:

OperationalError: no such column: MINUTE

Edward Radcliffe · Accepted Answer · 2022-04-08 20:15:44Z

1

From the PandaSQL documentation:

pandasql uses SQLite syntax.

The link in your post is for MySQL. Here is a reference for SQLite https://www.sqlite.org/lang.html

The syntax would be like:

"select ROUND((JULIANDAY(startDate) - JULIANDAY(completedDate)) * 1440) from df"

edited Apr 8, 2022 at 20:15

answered Apr 8, 2022 at 19:38

Edward Radcliffe

5872 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

x89 Over a year ago

which unit will the difference be here? and how can I change the unit?

Edward Radcliffe Over a year ago

It calculates the difference in days, multiplying it gives you your unit. * 24 would be hours, * 24 * 60 would be minutes * 24 * 60 * 60 would be seconds.

Collectives™ on Stack Overflow

find timestamp difference between 2 columns with sqldf

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related