How to make Chart application display this week until today data as default

Scriptcase 9
, ,
#1

I’m developing a chart application module with the {date} as dimension and {temperature} as metrics. A search field uses {date} and a date range as filters.

The question arises by setting up the <Default value of the start date/time> and <Default end date/time value> for the tab. I’ve always used a static text value for these fields but we’d like to improve the application by filling these fields with dynamic variables, where the start date/time has today minus 7 days date and the end date has today date, in this way user can modify start/end dates fields for other dates queries.
My first thought was to declare two PHP variables onApplication init event with these calculated dates and copy them to the search field on the tab.
The assignment of these variables to their respective fields in the tab form is proving to be a problem.
Any assistance will be well appreciated.

#2

Hi, in Mysql query you can do this for example

select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

Regards

1 Like
#3

SELECT date_sub(now(),INTERVAL 1 WEEK) AS wx;

If now() = 2023-02-02 09:23:44 result is: 2023-01-26 09:23:44 take 7 day from now()
If you are asking to have data on your chart only with information from Monday to Sunday on a current week this don’t work… Make a view with this type of Query and use for your Chart data.

SELECT *
FROM  database.table a
WHERE DATE_FORMAT(a.date_entered,'%Y%u') = DATE_FORMAT(CURDATE(),'%Y%u');

I HOPE THIS WORK FOR YOU.

Go to : MySQL DATE_FORMAT() Function FOR MORE INFOR ON DATE_FORMAT

%U => Week where Sunday is the first day of the week (00 to 53)

%u  => Week where Monday is the first day of the week (00 to 53)