How to select things group by weeks?

[bilipan]

Hi, everyone.
I want to know how to perform like this:

select DATE_FORMAT(create_date,'%Y%u') weeks, sum(total) countTotal from table_name
where in_out=2
GROUP BY weeks
order by weeks desc

Thanks in advanced.

[ujjwalguptaofficial]

You can select group by weeks using jsstore query, but you can not do formatting. You will have to do the formatting after the results is returned.

You can use middleware for formatting data - https://jsstore.net/tutorial/middleware/

[bilipan]

thanks，formatting is not essential for me, I just only want to select by week or month.
My data structure like this: every row of table has date column which format is like Fri Jul 22 18:53:21 2022 +0530.
So in jsstore, is it possible to query without formatting? or could you give a code sample?

[ujjwalguptaofficial]

oh i see, you are converting stored data into another form of data and want to run query on that data.

currently this is not supported but can be considered as feature. I will work on this later.

[ujjwalguptaofficial]

have created an issue here - #281

[ujjwalguptaofficial]

@bilipan you can use transaction for your scenario

https://jsstore.net/tutorial/transaction/

transaction allows you to perform complex scenario, you will be able to select data, filter it return the way you want.

[ujjwalguptaofficial]

@bilipan i have added new feature store in select api. Please check this issue page for more info - #281