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..
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 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Moderator: daj
-
- Posts: 2473
- 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
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');
I would test it first, though.
-
- Posts: 1808
- 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
Thanks freddy
I did manage to work it out in the end, with:
I used SELECT * before DELETE first, just to check it wouldn't do the wronbg thing.
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'
-
- Posts: 2473
- 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
That's good, well done. I've learned something too - I didn't know you could use 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.
Code: Select all
LogDateTime >= '2020-12-01 00:00'