I have a promotions page that queries a Promotions collection looking for all promotions for a particular store that are (.gt) the promotionStartDate and (.lt) the promotionEndDate. I am only getting results returned if I only use one parameter - . lt ( “promotionEndDate” , localDate ) . If I query using both . gt ( “promotionStartDate” , localDate ) and . lt ( “promotionEndDate” , localDate ) , as I have it in my code below, no results are returned.
I have been stuck on this all day. I’m sure it is a dumb mistake - any help would be appreciated! Thank you!
Thank you so much for responding, Qu4dri. I tried changing the data type but no luck. Do you happen to have any other ideas that I can try? Thank you so much!
Hi, it seems like your dates are somehow saved as text and not as date objects.
Inside the collection try to click on the promotions start/end date and manually pick the date using the interface. After this for example in your console the promotionStartDate should have a similar format as the _updatedDate just above instead of a normal text string.
If you get the fields to be of type Date instead of string, it would be easy to use the .gt and .lt in the query.
No luck with the date and time fields. In fact, in testing, I cannot get a query to successfully run using just:
const localDate = new Date ()
and
. lt ( “promotionEndDate” , localDate )
I have only been successful converting the date to a string and then running against a date placed in a text field. But that seems to only be working for one parameter - not both .lt and .gt.
Thank you so much for your time. I will keep working on it and let you know if I come up with anything.
Just an update…after recreating the collection and setting up the date field with the time, I was able to run the query using const localDate = new Date () and return results with one parameter.
But, I am still not able to chain both .lt and.gt.
To show you the differences with both method I have columns for the dates with or without time.
Here is the page code I use to query the promotions:
import wixData from 'wix-data';
$w.onReady(async function () {
console.log('Get promotions with date and time')
await getPromotionsWithDateTimeField()
console.log('Get promotions only with date')
await getPromotionsOnlyDate()
})
async function getPromotionsWithDateTimeField() {
//Get user current local date
let today = new Date()
//Create array of objects with active promotions
let activePromotions = await wixData.query('Promotions')
.lt('promotionStartTimeDate', today)
.gt('promotionEndTimeDate', today)
.find().then((results) => { return results.items })
//Print in the console the promotion details
if (activePromotions.length > 0) {
console.log('Active promotions:')
for (let promotion of activePromotions) {
console.log(promotion)
}
}
//Do other stuff
}
async function getPromotionsOnlyDate() {
//query all promotions
let allPromotions = await wixData.query('Promotions').find().then((results) => { return results.items })
//filter all promotions to get just the active promotions
let activePromotions = allPromotions.filter((promotion) => {
let today = new Date()
let startDate = new Date(promotion.promotionStartDate)
let endDate = new Date(promotion.promotionEndDate)
return (startDate < today && today < endDate)
})
//Print in the console the promotion details
if (activePromotions.length > 0) {
console.log('Active promotions:')
for (let promotion of activePromotions) {
console.log(promotion)
}
}
//Do other stuff
}
Pay attention about few things:
In the first function the .lt is assigned to the Starting date, and the .gt to the Ending date. (Which I understand is a bit misleading)
The result (activePromotions) is already a list of promotion objects containing only the active ones.
In the second function the date is in string format in the dataset, for this reason a manual filtering is needed as is not possible to compare a string with a date format.
The filter just creates date types from the string, which can be compared with a normal boolean operations.
Thank you so much, Qu4dri ! It works perfectly. I really appreciate you taking the time to break it down for me and explain. I have been stuck on this for days! I cannot thank you enough
