MySQL Bugs: #92776: Date error on select (returns one day before)

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:	MacOS (10.14)
Assigned to:		CPU Architecture:	x86 (intel i7)

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

Thank you for the bug report. Please provide a complete test case. Thanks.

====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==============

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.

Temporal solve
===========
Just change the reading variable to rs.getString(theField) and the date is correct

Thank you for the feedback. Closing according prior comment.

@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.

Thank you for the feedback. Then I didn't understand what meant your comment, you will provide what was asked?.

[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.

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".

This may be a duplicate of Bug#91112.