Stack Exchange
SparkSQL between timestamp in where(filter) clause (V.S. In MySQL)
Describe:
I have a table with timestamp column and i want to get the number of values where the timestamp in specific time window.  
My code is as shown in here:  
String startTime = "2018-08-08 00:00:00";
String endTime = "2018-08-08 23:59:59";
productDF.where("CREATETIME >= '" + startTime + "' AND CREATETIME <= '" + endTime + "'").count();

I also tried between...and...sentence; and also:
productDF.where(unix_timestamp(col("CREATETIME"), "yyyy-mm-dd hh:mm:ss")
                    .cast("timestamp")
                    .between(
                            Timestamp.valueOf(startTime),
                            Timestamp.valueOf(endTime)
                    )).count();

The result i get is 6843.  
But when i operate the sql sentence using Navicat:
SELECT COUNT(*) FROM my_table 
WHERE CREATETIME BETWEEN '2018-08-08 00:00:00' and '2018-08-08 23:59:59';

it shows 7689.
Problem:
I want to know why i get the different results in Spark and Mysql.....what am i missing here??

Problem solved!
The problem happened because of the TIMEZONE.
In spark env., it get the timezone from_unixtime..So need to set the config.  
.config("spark.sql.session.timeZone", "UTC")

But I still don't understand why the spark sql session flow the system timezone instead of just select from the column.....

The column has a TZ, like it or not. Just ask your Navicat thing to show the timestamps with the TZ used -- maybe the tool does not set the client-side TZ and uses whatever the server has for default... While Spark does, as is expected.

Recommended reading: "Falsehoods programmers believe about time"
https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time (2 parts)

Recommended reading: "Working with timezones" https://davecturner.github.io/2018/08/12/working-with-timezones.html

Also, note that the Spark property you are using is very recent, if I'm not mistaken. Not long ago, if your driver and executors were on different servers with different TZ set by default... It could get weird.
