Issue
My Use-cases:
We have an installation schedule entity (check below code) and it has an installation date.
Once installation has completed, after 4 weekdays we will verify the installation status with customers. Note: (4 weekdays - this count is configurable. So 'X' weekdays)
Weekdays means - Monday to Friday. We don't care about other holidays.
- I have a scheduler, it will retrieve these orders after 'X' weekdays - I'm stuck here
I don't know how to make a query for after 'X' weekdays.
My code:
@Entity
@Table(schema = "myschema", name = "installation_dates")
@Getter
@Setter
@NoArgsConstructor
public class InstallDates extends TransEntity implements Serializable {
// other columns
@Column(name = "installation_schedule_datetime")
private LocalDateTime installationScheduleDatetime;//I use this column for calculation
@Formula("getWeekDaysCount(installationScheduleDatetime)")
private int weekDaysCount;
public int getWeekDaysCount(LocalDateTime installationScheduleDatetime) {
int totalWeekDays = 0;
LocalDateTime todayDate = LocalDateTime.now();
while (!installationScheduleDatetime.isAfter(todayDate)) {
switch (installationScheduleDatetime.getDayOfWeek()) {
case FRIDAY:
case SATURDAY:
break;
default:
totalWeekDays++;
break;
}
installationScheduleDatetime = installationScheduleDatetime.plusDays(1);
}
return totalWeekDays;
}
}
Question:
How to make a SQL or JPQL or JPA query for weekdays?
I knew its very basic question, I am a mobile app developer, I recently joined the Springboard team, it's really hard for me :(
Feel free to give your valuable feedback!
Solution
I have a following suggestion if I correctly got the problem.
Java:
- Take the current date
- Find the
date of interest
: count minus 4 workdays (so if it is Friday today - subtract 4 days, if it is Monday - subtract 2 days for weekend and 4 more days for weekdays)
Then write a query that will select all installations that were done on the date of interest.
In pseudo code:
select * from installations where installation_date = <date of interest>;
.
Date of interest Java code:
public LocalDateTime getDateOfInterest(int workdays) {
LocalDateTime currentDate = LocalDateTime.now();
if (workdays < 1) {
return currentDate;
}
//it will subtract 'X' working days from current date
LocalDateTime result = currentDate;
int addedDays = 0;
while (addedDays < workdays) {
result = result.minusDays(1);
if (!(result.getDayOfWeek() == DayOfWeek.FRIDAY ||
result.getDayOfWeek() == DayOfWeek.SATURDAY)) {
++addedDays;
}
}
return result;
}
Answered By - vladtkachuk
Answer Checked By - Mary Flores (JavaFixing Volunteer)