Get the closest date to a date in a mysql query-CodePudding

So I have a table in MySQL which stores a name and a date. I want to write a query that gets the closest date to a certain date I have determined. For example, I have:

x = datetime(2022, 01, 01)

This:

query = "SELECT date_ FROM set_payment7777 GROUP BY name"
mycursor.execute(query)
for cursor in mycursor:
    print(cursor)

is currently printing all the dates from the table grouped by name. I want to add something to make it print all the dates for each name that is closer to the variable x.

For instance, if we have the entries in the table: "06-06-2021, James" also "06-07-2021, James" and "04-04-2021, Helen" also "05-04-2021, Helen" it should print: 06-07-2021 and 05-04-2021.

I hope you understand.

CodePudding user response：

If I can understand the problem you can try this solution using it on the result of your query:

import pandas as pd
from datetime import datetime

df = pd.DataFrame({'date': ['06/06/2021', '06/07/2021', '04/04/2021', '05/04/2021' ],
                'name': ['James', 'James', 'Helen', 'Helen']})
df['date'] = pd.to_datetime(df['date'])

data_point = datetime(2022, 1, 1)

df['data_diff'] = df['date'] - data_point

# abs data_diff
df['data_diff'] = df['data_diff'].abs()

# select the rows where data_diff is less than a month
df['data_end'] = df['data_diff'].apply(lambda x: x.days)

CodePudding user response：

Since the comparison date is greater than all the dates in the table, you can reduce the problem to finding the greatest date for each name:

SELECT name, MAX(date)
FROM set_payment7777
GROUP BY name