Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

SQL - Delete A Selected Month

Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

Moderator: daj

Post Reply
Mapantz
Posts: 1774
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

SQL - Delete A Selected Month

Post by Mapantz »

I've ran across an issue with some of my data in my 'Monthly' table in MySQL

I think I have found the issue, but I need to delete a specific month/year in PHPMyAdmin. Does anyone know an SQL query I can run to do that?

I don't fancy trying to do it manually.. :shock: :?
Image
freddie
Posts: 2433
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: SQL - Delete A Selected Month

Post by freddie »

Code: Select all

DELETE FROM Monthly WHERE LogDateTime > STR_TO_DATE('2021,2,28 23,59,59', '%Y,%m,%d %h,%i,%s') AND LogDateTime < STR_TO_DATE('2021,4,1 0,0,0', '%Y,%m,%d %h,%i,%s');
To delete March 2021.

I would test it first, though.
Freddie
Image
Mapantz
Posts: 1774
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

Re: SQL - Delete A Selected Month

Post by Mapantz »

Thanks freddy

I did manage to work it out in the end, with:

Code: Select all

DELETE
FROM Monthly
WHERE LogDateTime >= '2020-12-01 00:00' AND LogDateTime <= '2020-12-31 23:50'
I used SELECT * before DELETE first, just to check it wouldn't do the wronbg thing.
Image
freddie
Posts: 2433
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: SQL - Delete A Selected Month

Post by freddie »

That's good, well done. I've learned something too - I didn't know you could use

Code: Select all

LogDateTime >= '2020-12-01 00:00'
in your WHERE clause - I thought you needed to use the function STR_TO_DATE to turn the date string into a datetime database object.
Freddie
Image
Post Reply