Bug #92776 Date error on select (returns one day before)
Submitted: 14 Oct 2018 2:47 Modified: 17 Nov 2018 17:27
Reporter: Eduardo Silva Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.12 OS:Mac OS X (10.14)
Assigned to: CPU Architecture:x86 (intel i7)

[14 Oct 2018 2:47] Eduardo Silva
Description:
When making a query through mysql-connector-java-8.0.12 to a date field, the result is one day before what is saved on the database. 

This happens between 2018-05-13 and 2018-08-12, the again between 2019-05-12 and 2019-08-11

I'm ussing:
NetBeans IDE 8.2 (Build 201609300101)
mysql-connector-java-8.0.12 
mySql 8.0.12 database
the program is javafx

How to repeat:
NetBeans IDE 8.2 (Build 201609300101)
mysql-connector-java-8.0.12 
mySql 8.0.12 database
the program is javafx

query to a date value: e.g. 2018-05-15 and you will receive 2018-05-14 instead
[14 Oct 2018 22:25] Miguel Solorzano
Thank you for the bug report. Please provide a complete test case. Thanks.
[14 Oct 2018 22:43] Eduardo Silva
====MySQL table ===============
CREATE TABLE `fecha` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` date DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `area_id` (`area_id`),
  CONSTRAINT `area_id` FOREIGN KEY (`area_id`) REFERENCES `area` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
====END of MySQL Table =====

===Connection to Database======
Class.forName("com.mysql.cj.jdbc.Driver");
            
            String serverAddress = "localhost";
            String dataBase = "LCA";
            String dbURL = "jdbc:mysql://" + serverAddress + ":3306/" + dataBase + "?autoReconnect=true&useSSL=false";
====END of Connection to Database====

====The Query to the database====
Select f.id, f.fecha, f.area_id "
                          + "from fecha f "
                          + "   , area a "
                          + "where f.area_id = a.id "
                          + "  and a.rut_id = ? "
                          + "  and a.nombre = ? 
===END of Query to the database====

===The table with data for making the query===
-- MySQL dump 10.13  Distrib 8.0.12, for macos10.13 (x86_64)
--
-- Host: localhost    Database: LCA
-- ------------------------------------------------------
-- Server version	8.0.12

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `fecha`
--

DROP TABLE IF EXISTS `fecha`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `fecha` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` date DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `area_id` (`area_id`),
  CONSTRAINT `area_id` FOREIGN KEY (`area_id`) REFERENCES `area` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `fecha`
--

LOCK TABLES `fecha` WRITE;
/*!40000 ALTER TABLE `fecha` DISABLE KEYS */;
INSERT INTO `fecha` VALUES 
(1,'2018-05-10',7),
(2,'2018-05-11',7),
(3,'2018-05-12',7),
(4,'2018-05-13',5),
(5,'2018-05-14',7),
(6,'2018-08-11',7),
(7,'2018-08-12',7),
(8,'2018-08-13',7),
(9,'2018-10-08',9),
/*!40000 ALTER TABLE `fecha` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-10-14 19:38:55
====END of the table==============
[15 Oct 2018 9:28] Filipe Silva
Hi Eduardo,

This is happening because the time zone setting in server is different than the client's time zone.

Please provide more details about both if you need more help. We would also need a small java test code for us to see exactly how you are fetching the data.
[15 Oct 2018 16:51] Eduardo Silva
Temporal solve
===========
Just change the reading variable to rs.getString(theField) and the date is correct
[15 Oct 2018 20:15] Miguel Solorzano
Thank you for the feedback. Closing according prior comment.
[15 Oct 2018 22:48] Eduardo Silva
@Miguel Solorzano
Sorry, but this couldn't be the problem, since the query and the database are both under the same machine and no virtual server is involved.
[16 Oct 2018 1:24] Miguel Solorzano
Thank you for the feedback. Then I didn't understand what meant your comment, you will provide what was asked?.
[17 Oct 2018 17:27] Miguel Solorzano
[15 Oct 9:28] Filipe Silva

Please provide more details about both if you need more help. We would also need a small java test code for us to see exactly how you are fetching the data.
[18 Nov 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 Dec 2018 16:16] Filipe Silva
This may be a duplicate of Bug#91112.