Have a MySQL database whose default timezone is the system timezone on Linux which is UTC.
CREATE TABLE `event_schedule` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(30) DEFAULT NULL,
`event_id` varchar(30) NOT NULL DEFAULT,
`event_type_id` varchar(30) NOT NULL DEFAULT,
`event_date` datetime DEFAULT NULL,
`event_date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)
The content is as follows:
| 1 | karoake | aab3223 | 2 | 2017-08-15 00:00:00 | 2017-08-15 16:00:00 |
| 2 | comedy | cce8465 | 3 | 2017-08-25 00:00:00 | 2017-08-25 19:00:00 |
Inside my Spring JDBC query, I am getting the current event like this:
private String getEventIdFromEventDay(String eventTypeId) {
String eventId = "";
SqlRowSet resultSet = null;
try {
resultSet = jdbcTemplate.queryForRowSet(
"select * from event_schedule where event_date >= CURDATE() and event_type_id=? order by event_date limit 1;",
new Object[] { eventTypeId });
if (resultSet != null && resultSet.next()) {
eventId = resultSet.getString("event_id");
}
}
}
catch (Exception e) {
logger.error("Exception om getEventIdFromEventDay: " + e);
}
return eventId;
}
So, the situation is this:
If an event is on a particular day, for example, if the comedy event is on 2017-08-24 starting at 7:00 p.m. PST in my database it's in UTC time:
| 2017-08-25 00:00:00 | 2017-08-25 19:00:00 |
So, for every event that is starting at 5:00 p.m. PST, or after, this query works.
However, let's say that an event starts at 2017-08-15 4:00 p.m. PST (which means it's 11:00 p.m. UTC), this query only works until 4:59 p.m. PST, because at 5:00 p.m PST, the System clock in MySQL becomes midnight (resulting in the next day). The only way to work around this was to manually update that date to the next day 2017-08-16, for this query to work.
Am wondering if there's a better query for what I am trying to do...
Tried this:
select * from event_schedule where CONVERT_TZ(event_date,'+00:00','-07:00') >= CONVERT_TZ(CURDATE(),'+00:00','-07:00') and CONVERT_TZ(TIMESTAMP(event_date, '23:59:59'),'+00:00','-07:00') <= CONVERT_TZ(TIMESTAMP(CURDATE(), '23:59:59'),'+00:00','-07:00') and event_type_id = "2";
Yields in:
Empty set (0.00 sec)
Does anyone know how I can fix this?
Edited after reading Basil's suggestions
Used the ThreeTen-Backport library in my file and tried this):
// Get current time in UTC
Instant now = Instant.now();
// Get time zone in PST
ZoneId pstTimeZone = ZoneId.of("America/Los_Angeles");
// Adjust into that time zone from UTC, producing a ZonedDateTime object.
ZonedDateTime adjustedZDTimeZoneFromUtc =now.atZone(pstTimeZone);
// Extract Date Only Value
LocalDate localDate = adjustedZDTimeZoneFromUtc.toLocalDate();
// Determine the first moment of the following day for that timezone
ZonedDateTime zdt = localDate.plusDays(1).atStartOfDay(pstTimeZone);
// Adjust back to UTC from that Time Zone
Instant firstMomentOfTomorrow = zdt.toInstant();
System.out.println("\n\n\t\tFirst moment of tomorrow: " + firstMomentOfTomorrow + "\n\n");
This is the output:
First moment of tomorrow: 2017-10-25T07:00:00Z
So, when trying to use the first moment of tomorrow's date inside a raw SQL call (in order to test it before putting it back inside my Java code):
SELECT * FROM event_schedule WHERE event_date >= now() AND event_date <= "2017-10-25T07:00:00Z";
It yields:
Empty set, 1 warning (0.00 sec)
Tried Basil's query like this as well:
SELECT * FROM event_schedule WHERE when >= now() AND when < "2017-10-25T07:00:00Z";
Yields in:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when >= now() AND when < "2017-10-25T07:00:00Z"' at line 1
Questions:
Am not a MySQL guru so is "when" a keyword that can be used?
Should I create a column called
first_moment_of_tomorrowand inside my event table for each row and do date range check?
The issue with select now() vs. curdate() is that now brings back the actual date with actual seconds and whereas event_date is in a format like this:
2017-08-15 00:00:00
So, when trying curdate:
select curdate();
Yields this:
+------------+
| curdate() |
+------------+
| 2017-10-24 |
+------------+
1 row in set (0.01 sec)
And when trying now():
select now();
Yields this:
+---------------------+
| now() |
+---------------------+
| 2017-10-24 18:27:37 |
+---------------------+
1 row in set (0.00 sec)
Does anyone understand what the issue is? I need to do some type of SQL query against event_schedule.event_date but am somewhat confused now...
:(
