dimanche 20 décembre 2020

Update columns based on calculation

My table looks like this:

    id     entry_date          
    1      21/12/2020 15:00          
    1      21/12/2020 17:00          
    1      21/12/2020 19:00          
    2      24/12/2020 00:00         
    2      24/12/2020 12:00

I have a list of id's connected to datestamps. I can manage to calculate the difference between their latest and first entry as follows:

SELECT id, TIMESTAMPDIFF(hour, MIN(entry_date), MAX(entry_date))
FROM mytable
GROUP BY id;

However, I am unsure how I can update my table to reflect these calculations. What I want is the following:

id     entry_date          time_difference
1      21/12/2020 15:00          4
1      21/12/2020 17:00          4
1      21/12/2020 19:00          4
2      24/12/2020 00:00          12
2      24/12/2020 12:00          12



Aucun commentaire:

Enregistrer un commentaire