I’ve got a database with a datetime field showing every day for the next 2 years. These dates are repeated for every name in another field. e.g.
8/28/2021 Mike
8/29/2021 Mike
…
8/28/2021 Bob
8/29/2021 Bob
…
I’m laying out the repeater connected to this data to look like a calendar. each row has 7 columns and the first column starts on the first date of my data. Instead of having to delete data out of the database 7 days at a time, how can I filter the data to start with all dates greater than the latest Sunday?
I’m using the editor and the only filter you can add for datetime field is “isempty” or isnotempty". I’m OK with using code to filter my repeater data but this leaves me with solving my next issue, how do I get the latest Sunday date? Thanks in advance for anyone willing to help solve!!!
The “latest” (or the most recently passed) Sunday date would be the the current date - the day you are in now. So, If the date was the 25th and it was a Weds, then
var date = new Date(year);
let weekday = date.getDay();
So, now you have the current date and the day of week (the get date should return 3). Now, subtract three days from today with something like
sunday = date.setDate(date.getDate() - week)
I haven’t tested the code, so have a play.
Thanks for your response. I get the logic but I think I also need to research up on how “new Date” and “getDay” and “getDate” all work.
Anyone else have a “code for dummies” solution?
No prob, all the best. Console.log is your friend for this… e.g. today = new Date() console.log(today).
Got this to work:
let date = new Date ();
let todayNum = date . getDay ()+ 1 ;
date . setDate ( date . getDate () - todayNum );
in my dataquery i use
.gt(“schedule”,date)
If you console.log(date), you actually get Saturday. I have feeling that the time is going to mess me up.
Why the +1? If the day is Sunday then you return 1. So, if the date is sunday then you will get Saturday. And you are doing a GT search so if Sunday returns a Sunday then you will get the Monday, Tues etc which I think is what you want. Try removing the +1, although I may be missing a trick. Glad you worked it out.
I’ve got the repeater laid out like a calendar starting on Sunday. So instead of subtracting today’s date # (Sun 0, Mon 1, Tue 2, etc). I’m subtracting today’s date # + 1 to result in Saturday’s date. Greater than Saturday gives me all dates starting with Sunday.
The problem I’m going to have is all of my dates in the database have a time component of 10am. So everyday from midnight to 10am the data greater than now - today’s date number + 1 could result in giving me back Saturday.
Just typing this out has me confused. Let me work this out…
Currently 7pm on Monday. Date = Today - (1 for monday + 1)
so date = Saturday at 7pm. All dates greater than that start with Sunday Aug 29th at 10am.
Tomorrow morning at say…7am. Date = Today - (2 for Tuesday + 1) so date = Saturday at 7am. All dates greater than that are now start with Saturday, Aug. 28th at 10am.
Argh!!!
Ah, “starting with Sunday.” that’s cool. Misread that sorry. Dates do my head in too as I have dyslexia around dates/times and some numbers. You could look at doing your arithmetic without the time. Do things work better when you work from midnight? Or you could set the date to the next midnight by date.setHours(24,0,0,0), so your Saturday at 7am would become Sunday midnight. To get these things right with my dyslexia I have to chart them out on a piece of paper with lines and dates on them. I haven’t done this here. I hope tho this additional info helps?
date.setHours(0,0,0,0);
This may also help you play about https://playcode.io/online-javascript-editor you can do the date manipulation and get instant results without having to play about in the WIX editor.
Great idea!
You are not alone in the datetime confusion! Add in the challenge of Wix converting all time to Zulu and then having to convert it back. It’s maddening.
I’ll play around with this but I think you are spot on with the strategy of converting to midnight. Every night at midnight the “.gt(date)” keeps pushing back to Sunday 0:00:00…then my query will always grab the latest Sunday 10am date every time.
You the man!!! Your setHours suggestion is going to work perfect and the playcode editor is awesome!!! Bookmarked!!!
Here is my final code to get the latest Sunday for anyone else needing something like this…
let date = new Date (); //stores current date time into ‘date’
let todayNum = date . getDay (); //stores the day of the week number of ‘date’ (note: Sunday = 0)
date . setDate ( date . getDate () - todayNum ); //resets ‘date’ to today’s date minus the number of days into the week today is
date . setHours ( 0 , 0 , 0 , 0 ); //resets ‘date’ hours to midnight
console . log ( "is this sunday’s date? " + date ); // writes to console
I can now use ‘date’ in my query to get all dates greater than the most recent Sunday. And since all my dates in the database have a time component of 10am the Sunday will always be the first date greater than ‘date’.
Kyle…Thanks again for sticking with me and offering up suggestions for me to learn!!!
Just as a small tip, I really like using something like Moment.JS as a date manipulator, it is way better than using Date() from Vanilla JavaScript.
Your code would be this:
import moment from "moment"
let startDate = moment().startOf("week").format() //Always last sunday
let endDate = moment().format() //today
Nice - I’m gonna dig into that thanks Bruno,
@mike91901 No prob at all. I get dates so wrong that my wife has to double check all my flight bookings as I book things on wrong days and even get airports incorrect.
HA! Seriously you just reduced all of the code to one line?
let startDate = moment (). startOf ( “week” ). format () //Always last sunday
That’s hilarious. I’ve been rubbing sticks together to make fire and Bruno just showed up with a flamethrower!
I’m definitely looking into Moment.JS. Bruno! You da man!