The Answer by Albe looks correct to me. Here are some further thoughts of mine.
I want my application to set a flag on the event expired = true.
As Albe mentions, there is no need to record this fact of expiration. Simply query by the date to find records before a date (expired) or after a date (not expired).
If performance is a concern, test to verify. If it is an issue, consider indexing the date column.
How do I deal with timezones?
That is the trick. I'll be brief here, as this has been handled many times already here and on the sister site, https://dba.stackexchange.com.
First, get clear if your business requirements need a granularity of date or a moment. A date is imprecise as you intuit. For any given moment, the date varies around the globe by time zone. A few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.
If you care about moments, use a column of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE. Understand that the SQL standard barely touches on the subject of date-time handling. So behavior in different database systems varies widely. In Postgres, a value in that column is always in UTC. Any time zone or offset-from-UTC info provided with an input is used to adjust into UTC, then discarded. So if you care about the original zone/offset, you must manually store that in an additional column.
When retrieved, the value from that column is always in UTC. However the access tool you are using may inject its opinion about how to present that value. Some tools will dynamically apply a time zone. I find this behavior to be an unfortunate design choice. Though well-intentioned that behavior creates the illusion of that zone being part of the stored data.
In Java, use only the modern java.time classes, not the terribly troublesome and flawed old date-time classes bundled with the earliest versions of Java.
As of JDBC 4.2, you can directly exchange java.time objects with your database using PreparedStatement::setObject and ResultSet::getObject.
my server is located in Europe
The location of your server, and the current default time zone of the server’s OS and JVM, should be irrelevant to your programming and database work. Never depend on those default settings as they are out of your control and can be changed at any moment during runtime.
Instead, always specify your desired/expected time zone explicitly. Generally, most of your work should be in UTC. Adjust into a time zone only when required by business rules or for presentation to the user.
LocalDate ld = LocalDate.of( 2018 , Month.JANUARY , 23 ) ;
ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = ld.atStartOfDay( z ) ;
Adjust back into UTC by extracting an Instant. An Instant is what you should be exchanging with your database.
Instant instant = zdt.toInstant() ;
Adjust back into a zone.
ZonedDateTime zdtAuckland = instant.atZone( ZoneId.of( "Pacific/Auckland" ) ; // Same moment, same point on the timeline, different wall-clock time.
For querying on moments, search Stack Overflow to learn about the Half-Open approach where a span-of-time is defined where the beginning is inclusive while the ending is exclusive. This means you do not use the SQL operator BETWEEN. For example, see this.
Tip: Search on these class names and concepts using a search engine with a site:stackoverflow.com criterion. The search feature built into Stack Overflow is unfortunately skewed towards the Questions while ignoring the Answers. For example: https://duckduckgo.com/?q=site%3Astackoverflow.com+%2B%22Half-Open%22+%2Bjava&t=osx&ia=web
About java.time
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.
