Java Spring MySQL String Timezone column between two milliseconds

Question

I got a stored Timezone date as String on a MySQL Column with the next format:

2018-07-23T20:54:37.242Z --> start_date

What I want to do is a Between two milliseconds(or dates) like this:

SELECT * FROM activity_entity WHERE start_date BETWEEN 1532322000000 AND 1532408399000

Else, I'm using Java Spring Repository as backend where I send the parameters like this:

Date since = new Date(accessRepPOSTInDto.getSince()); //1532322000000 gives Mon Jul 23 00:00:00 CDT 2018
Date to = new Date(accessRepPOSTInDto.getTo());//1532408399000 gives Mon Jul 23 23:59:59 CDT 2018

@Query(value = "SELECT * FROM activity_entity WHERE start_date BETWEEN :since AND :too , nativeQuery = true)
ActivityEntity findBetweenDates(@Param("since") Date since, @Param("too") Date too);

Doing this returns null;

I thought MySQL can automatically format the two dates and the String column to do the Between but it looks like it doesn't.

Any help will be really grateful. Regards.

score 1 · Accepted Answer · answered Jul 24 '18 at 18:10

1

In your native query, you need to explicitly cast the value of your varchar column to the proper date/timestamp to be evaluated by the between operator. This is how your native query should look like:

SELECT * FROM activity_entity WHERE STR_TO_DATE(start_date, '%Y-%c-%eT%H:%i:%s.%fZ') BETWEEN :since AND :too

answered Jul 24 '18 at 18:10

Luiggi Mendoza

85,076
16
154
332

Thanks for the answered @LuiggiMendoza i will try it, as a doubt, the :since and too should be used as **double 1532322000000** or **Date Mon Jul 23 00:00:00 CDT 2018**. Regards. – Ramiro Arizpe Giacomelli Jul 24 '18 at 18:16
1

@ElíGiacomelli the parameters are `date` already, they don't need any cast. – Luiggi Mendoza Jul 24 '18 at 18:16

Basil Bourque · Answer 2 · 2018-07-24T20:09:15.633

tl;dr

SQL:

SELECT * FROM tbl WHERE event >= ? AND event < ? ;  -- Using Half-Open approach where beginning is *inclusive* while the ending is *exclusive*.

Java:

myPreparedStatement.setString( 1 , Instant.ofEpochMilli( 1_532_322_000_000L ).toString() ) ;
myPreparedStatement.setString( 2 , Instant.ofEpochMilli( 1_532_408_399_000L ).toString() ) ;

ISO 8601

2018-07-23T20:54:37.242Z

Text in this format is abiding by the ISO 8601 standard. That standard is the best way to represent date-time values as text. But in a database you should be using a purpose-built data type, defining a column of type akin to the SQL-standard TIMESTAMP WITH TIME ZONE. Search Stack Overflow for much more info.

The java.time classes use ISO 8601 formats by default when parsing/generating strings. So no need to specify a formatting pattern.

Instant instant = Instant.parse( "2018-07-23T20:54:37.242Z" ) ;

Count-from-epoch

You can convert your count of milliseconds since the epoch reference of first moment of 1970 in UTC using the Instant class.

Instant start = Instant.ofEpochMilli( 1_532_322_000_000L ) ;
Instant stop = Instant.ofEpochMilli( 1_532_408_399_000L ) ;

Generate strings in standard ISO 8601 format used in your database column.

String startStr = start.toString() ;
String stopStr = stop.toString() ;

Avoid legacy date-time classes

The old date-time classes that were bundled with the earliest versions of Java are bloody awful. Never use them. They have been supplanted entirely by the java.time classes.

Half-Open

What I want to do is a Between

The BETWEEN command in SQL should generally not be used with date-time values. That command is fully “closed” meaning both the beginning and the ending are inclusive.

Instead, for date-time work, it is generally best to define a span-of-time as Half-Open. In this approach the beginning is inclusive while the ending is exclusive. For example, students dismissed for lunch break from noon to 1 PM are expected back in their seats before the clock strikes 1 and the bell rings. Another example, a week starts on a Monday and runs up to, but does not include, the following Monday.

In SQL code, this means a query uses >=, AND, and <.

SELECT *
FROM tbl
WHERE event >= ?
AND event < ?
;

Since ISO 8601 format with the Z is chronological when sorted alphabetically, you can make this work with your ISO 8601 strings.

myPreparedStatement.setString( 1 , startStr ) ;
myPreparedStatement.setString( 2 , stopStr ) ;

If you had used a TIMESTAMP WITH TIME ZONE column type as discussed above, you would simply pass the Instant objects.

myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , stop ) ;

If you really must use fully-closed approach, adjust the query operators >=, AND, and <=. Or call BETWEEN.

I am not a Spring user, cannot help you there.

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?

Java SE 8, Java SE 9, Java SE 10, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

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.