mysql date add with query for the interval

[Cowboy_BeBa]

hi all

im having a bit of trouble creating a query to help me keep my data up to date
basically im testing a page ive built that displays data within a certain date range, ive got queries to populate my tables with relevant test data but the problem is keeping this data up to date.
I wound up running the following query as a quick fix

UPDATE tbl_batches SET StartDate = DATE_ADD(StartDate, INTERVAL 3 day), EndDate = DATE_ADD(EndDate, INTERVAL 3 day);

I added 3 days because 3 days is the number of days since the test data was valid, so by adding it the test data becomes valid again.

This worked fine, but i needed to update the number of days manually, so i decided surely there has to be a way to automate it.
I figured if i used the date dif function to get the number of days between now() and the earliest date in tbl_batches it should keep my data up to date every time i run it (the earliest date in tbl_batches because that was the earliest date the data was valid when i created my test data, adjusting all the dates but that different replicates my test data and allows me to see if the page is working correctly)

so with that in mind i constructed this nested query

 select DateDiff(now(), (select Min(StartDate) from tbl_batches)) as diff From tbl_batches group by diff;

this query generates the exact number of days i need to add.

so far all this took me about 10 minutes to figure out
the next part, i thought, would be simple
delete the intger from the dateadd function and copy paste my query in its place. hence this

 UPDATE tbl_batches SET StartDate = DATE_ADD(StartDate, 	INTERVAL (select DateDiff(now(), (select Min(StartDate) from tbl_batches)) as diff From tbl_batches group by diff) day);

just to clear it up below is the same code, just spaced out a bit

UPDATE tbl_batches SET StartDate = 
DATE_ADD
(
	StartDate, 
	INTERVAL 
	(
		select DateDiff
		(
			now(), 
			(
			select Min(StartDate) from tbl_batches
			)
		) as diff From tbl_batches group by diff
	) day
)

now my problem is ive spent the past several hours rewriting this dozens of times and i cant figure out why this isnt working. tbh ive probably spent more time than i should have on this query (updating the values manually would have taken far less time), but at this point i just want to learn what im doing wrong so i wont make the same mistake in the future

can anyone see whats going wrong here?

 

[plog]

im having a bit of trouble creating a query to help me keep my data up to date
basically im testing a page ive built that displays data within a certain date range, ive got queries to populate my tables with relevant test data but the problem is keeping this data up to date.

I just can't get my head around the big picture. Mostly I don't understand the purpose of your UPDATE query. Why must data be changed?

The way I understand this is that you ultimately want to test a page. Presumably this page is pulling data from your database via a SELECT query. You want to test pulling a specific date range which means that SELECT has a WHERE clause with those ranges.

Why even bother with an UPDATE query? Why not just configure the WHERE in your SELECT to work with the data you have? How long/often is this testing going to take place? I fail to see why you need a process to constantly UPDATE your data if all you are doing that for is testing.

 

[Cowboy_BeBa]

the page im testing is meant to only show data with a date range from now() til now()+7 days (basically records in the oncoming week)

so most of the records today wont be valid tomorrow and will not show up on the page at all

i could change the query but that defeats the purpose of testing it, all i want is to be able to update the data so i wont have to write new data all the time (the page is dependent on 3 tables, all of which require data that matches each other, it is a pain in the ass to write up data that matches. i already have data that matches so changing the date is the simplest option)

Like ive already said, ive already spent too much time on this query, it is not worth it, but i still want to know what im doing wrong so i learn from this mistake

 

[isladogs]

i could change the query but that defeats the purpose of testing it, all i want is to be able to update the data so i wont have to write new data all the time (the page is dependent on 3 tables, all of which require data that matches each other, it is a pain in the ass to write up data that matches. i already have data that matches so changing the date is the simplest option)

What also confuses me is why you need to keep retesting with data that you've already used for testing. If it worked before, surely it will work again next time (or vice versa)

As for your query, you have the notation wrong
Firstly its DateAdd not Date_Add
Secondly the syntax is

DateAdd(interval, number, date)

For example :

DateAdd(d, 3, StartDate)

So try this:

UPDATE tbl_batches SET StartDate = DateAdd(d, 3, StartDate), EndDate = DateAdd(d, 3, EndDate);

 

[Cowboy_BeBa]

nope, this is mysql, its dateadd(date, interval x day)
as i said i tested the first query UPDATE tbl_batches SET StartDate = DATE_ADD(StartDate, INTERVAL 3 day), EndDate = DATE_ADD(EndDate, INTERVAL 3 day); and it worked perfectly, so not the notation

and its not the data im testing its the page. it generates a lot of graphs and information and im testing those

i know howthe graphs are meant to look with the dataset i have, so im trying to get those working properly

 

[isladogs]

nope, this is mysql, its dateadd(date, interval x day)

Ah - I don't think you mentioned that before.
I don't use mySQL but is it DATE_ADD or DateAdd?

 

[Cowboy_BeBa]

lol, sorry to sound like a smart ass but first word of the threads title

youre right though, i shouldve mentioned it in the main text

 

[isladogs]

My bad but I rarely read the title of a thread!

 

[plog]

So you are having with the last code you posted in your initial post--the UPDATE query with with 2 nested SELECTS. You say its not working--what does that mean? Getting an error message? Usually phpAdmin gives you a specific line.

When I write action queries (UPDATE, INSERT, etc.) I start it off as a SELECT so that I know what records I am working with. Further, I work inside out when I have nested queries.

So I would take the innermost SELECT query and run it. When that works, bring in the next SELECT. Finally, bring in the UPDATE but make it a SELECT to show you both the new value and the current value of the fields you are updating.