Prisma•8mo ago

Query based on Time

I've a collection where I save date and time of the users last access. I have to send a notification exactly on same date and time next month. How can I write the query for it? I already have a cron job setup. I just want to know how to write the query for it. I've previously worked with date, but I'm not sure how to query based on date and time. Any help much appreciated. Thank you ..

3 Replies

SatishOP•8mo ago

I've a cron job which runs every 30 minutes. I want to fetch all the users who last accessed the app in that period(last month) to be notified. I hope this query works?

 // Get the current date and time
  const currentDate = new Date();

  // Calculate the start and end dates for last month
  const lastMonthStartDate = new Date(currentDate);
  lastMonthStartDate.setMonth(lastMonthStartDate.getMonth() - 1);
  lastMonthStartDate.setHours(currentDate.getHours(), currentDate.getMinutes() - 30, 0, 0); // Adjust for 30 minutes earlier

  const lastMonthEndDate = new Date(currentDate);
  lastMonthEndDate.setMonth(lastMonthEndDate.getMonth() - 1);
  lastMonthEndDate.setHours(currentDate.getHours(), currentDate.getMinutes(), 0, 0);

  // Fetch users who logged in between last month at this time and last month at this time - 30 minutes
  const users = await prisma.user.findMany({
    where: {


          loggedInAt: {
            gte: lastMonthStartDate,
            lt: lastMonthEndDate
          }
 

    }
  });

  return users;

 // Get the current date and time
  const currentDate = new Date();

  // Calculate the start and end dates for last month
  const lastMonthStartDate = new Date(currentDate);
  lastMonthStartDate.setMonth(lastMonthStartDate.getMonth() - 1);
  lastMonthStartDate.setHours(currentDate.getHours(), currentDate.getMinutes() - 30, 0, 0); // Adjust for 30 minutes earlier

  const lastMonthEndDate = new Date(currentDate);
  lastMonthEndDate.setMonth(lastMonthEndDate.getMonth() - 1);
  lastMonthEndDate.setHours(currentDate.getHours(), currentDate.getMinutes(), 0, 0);

  // Fetch users who logged in between last month at this time and last month at this time - 30 minutes
  const users = await prisma.user.findMany({
    where: {


          loggedInAt: {
            gte: lastMonthStartDate,
            lt: lastMonthEndDate
          }
 

    }
  });

  return users;

fotoflo•8mo ago

do you need the AND?

SatishOP•8mo ago

Ah .. it doesn't make sense to have it!! Thank you for pointing out. I'm editing it now. @fotoflo Do you think the logic is correct?

Gaming

Programming

Query based on Time