Postgresql returns day-of-week using EXTRACT with dow in the following fashion: 0 is Sunday, 1 is Monday, all the way to 6 which is Saturday, see official documentation.
I am looking for some official constants either in Java proper or in JDBC or perhaps some standard library (Apache Commons?) that match this numbering.
According to the same official documentation you should use isodow:
The day of the week as Monday (1) to Sunday (7)
I am looking for some official constants either in Java proper
Yes, Java defines the seven day-of-week values in the DayOfWeek enum using the same standard ISO 8601 definition as the isodow function in Postgres where a week runs from Monday-Sunday with days numbered 1-7.
DayOfWeek.WEDNESDAY.getValue() // Returns an `int` 1-7 for Monday-Sunday.
3
java.time.DayOfWeek enumAs the Answer by Rcordoval suggests, use the Postgres function isodow.
The “iso” in isodow refers to ISO 8601 standard and its definition of week where days are numbered 1-7 for Monday-Sunday.
Java supports that same definition of week in its DayOfWeek enum. That class defines seven objects for you, one for each day of the week such as DayOfWeek.MONDAY. To get each object’s day-of-week number, 1-7 for Monday-Sunday, call DayOfWeek::getValue. But in your Java coding, focus on using and passing around the DayOfWeek objects themselves rather than mere integer numbers.
int dowNumber = DayOfWeek.WEDNESDAY.getValue() ;
3
Going from the integer number to a DayOfWeek object is bit trickier, as we need to access a Java array using an index. The index means zero-based counting, so subtract one from your day-of-week number. Wednesday is day # 3, so subtract 1 for a result of 2 to access DayOfWeek.WEDNESDAY.
DayOfWeek dow = DayOfWeek.values()[ 3-1 ] ; // Get object named `WEDNESDAY` using zero-based index counting, so 3 - 1 = 2.
dow.toString(): WEDNESDAY
By the way, to automatically localize the name of the day-of-week, call DayOfWeek::getDisplayName.
I am currently writing a query that is finding customers that currently have a Friday 10:00 AM in their respective time zone, so I need to pass in the concept of "Friday" into the query and of course I don't want to hard-code it.
May be better to do this work in Java using the industry-leading java.time classes rather than in SQL.
You must be very careful about time zones. Be explicit with time zones rather than relying on implicit default zones.
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.
If no time zone is specified, the JVM implicitly applies its current default time zone. That default may change at any moment, so your results may vary. Better to specify your desired/expected time zone explicitly as an argument.
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 ) ;
If you want to use the JVM’s current default time zone, ask for it and pass as an argument. If omitted, the JVM’s current default is applied implicitly. Better to be explicit, as the default may be changed at any moment during runtime by any code in any thread of any app within the JVM.
ZoneId z = ZoneId.systemDefault() ; // Get JVM’s current default time zone.
Next, get the following Friday, or stick with today if it is already a Friday. Use a TemporalAdjuster to adjust between dates, specifically the one found in TemporalAdjusters.
LocalDate nextOrSameFriday = today.with( TemporalAdjusters.nextOrSame( DayOfWeek.FRIDAY ) ) ;
You said you were aiming at 10 AM.
LocalTime lt = LocalTime.of( 10 , 0 ) ; // 10 AM.
Combine with date and zone to get a specific moment.
ZonedDateTime zdt = ZonedDateTime.of( ld , lt , z ) ;
Adjust into UTC by extracting a Instant object. Same moment, same point on the timeline, but different wall-clock time.
Instant instant = zdt.toInstant() ;
Formulate your SQL to query your database column of type TIMESTAMP WITH TIME ZONE.
String sql = "SELECT * from tbl WHERE when_col = ? ; " ;
…
myPreparedStatement.setObject( … , instant ) ;
And retrieval.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
ZonedDateTime zdt = instant.atZone( z ) ;
In this approach you have need for the dow or isodow functions in Postgres.
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.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
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.
You can use the Calendar constants minus 1 so you can match the PostgreSQL values ie: Calendar.SUNDAY = 1
