I am storing some days and hours as timestamp in my MySQL table. What I want to do now is retrieve every day that a specific user worked from last Sunday until the Sunday before (One week of work). And the other option is retrieve every day from today until last Sunday (current week). Some employees does not work everyday.
I don't know how I can code this in Java or whether there is a specific MySQL function.
in Java, you can put the Calendar class to good use to find the previous Sunday :
Calendar c = Calendar;
c.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
Date lastSunday = c.getTime();
you can then decrement by 1 WEEK_OF_MONTH to find the Sunday before that :
c.add(Calendar.WEEK_OF_MONTH, -1);
Date sundayBeforeThat = c.getTime();
LocalDate.now( ZoneId.of( "Pacific/Auckland" ) )
.with( TemporalAdjusters.previous( DayOfWeek.SUNDAY ) )
The modern approach uses the java.time classes.
Remember that the TIMESTAMP data type in MySQL is in UTC time zone.
In contrast, you want particular dates for particular day-of-week value where a particular time zone is critical. A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.
Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).
ZoneId z = ZoneId.of( "America/Montreal" );
LocalDate today = LocalDate.now( z );
Let's get the last Sunday, excluding today if it were a Sunday. Use a TemporalAdjuster implementation found in the TemporalAdjusters (note plural) class. Specify a DayOfWeek enum object.
LocalDate previousSunday = today.with( TemporalAdjusters.previous( DayOfWeek.SUNDAY ) ) ;
Get the Sunday further back.
LocalDate startingSunday = previousSunday.minusWeeks( 1 ) ;
We need exact moment, not entire date. Get the first moment of the day. Do not assume that means the time-of-day of 00:00:00. Anomalies such as Daylight Saving Time (DST) means that the day might begin at a time like 01:00:00. Let java.time figure that out.
ZonedDateTime zdtStart = startingSunday.atStartOfDay( z ) ;
Next we need the ending time. Actually, not the ending time. Determining the split second ending of the week is tricky. The common practice in date-time work is to use the Half-Open approach to defining a span of time. In Half-Open, the beginning is inclusive while the ending is exclusive. That means we want to start at the first moment of one Sunday and run up to, but not include, the first moment of the following Sunday.
Note that you cannot use BETWEEN in SQL for Half-Open, as that command is Closed, meaning both beginning and ending are inclusive.
ZonedDateTime zdtStop = previousSunday.atStartOfDay( z ) ;
The query in MySQL will be done in UTC. So let's extract Instant objects from the ZonedDateTime objects as they are always in UTC by definition.
Instant start = zdtStart.toInstant() ;
Instant stop = zdtStop.toInstant() ;
If your JDBC driver supports JDBC 4.2 and later, you can directly use these Instant objects in your query via PreparedStatement::setObject and ResultSet::getObject.
This SQL code is untested, but should point you in the right direction.
String sql = "SELECT * from tbl_ WHERE col_ >= ? AND col_ < ? ; " ;
PreparedStatement pstmt = conn.prepareStatement( sql ) ;
pstmt.setObject( 1 , start ) ;
pstmt.setObject( 2 , stop ) ;
ResultSet rs = pstmt.executeQuery() ;
…
Instant instant = rs.getObject( … , Instant.class ) ;
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
