Hot questions for Using Joda-Time in mysql

Question:

Mysql date time is like this: 2015-05-01 21:36:38.0

And joda current time:

DateTime now = new DateTime().toDateTime();
outputs
2015-05-01T22:08:15.705+02:00

How to get the difference between these 2 dates 2015-05-01 21:36:38.0 and 2015-05-01T22:08:15.705+02:00 in minutes?


Answer:

You need to create a DateTimeFormatter for your MySQL timestamp. Here is an example:

     DateTimeFormatter formatter = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss.S");
    String mysqldatetime = "2015-05-01 21:36:38.0";
    DateTime mysqldt = formatter.parseDateTime(mysqldatetime); 

    DateTime now = new DateTime().toDateTime();
    Period diff = new Period(now,mysqldt);
    System.out.println(diff.getMinutes());

Question:

I'm having a weird problem right now. When I insert todays date into database, it's mapped into yesterdays date, it happens with every date. For example, when I try to insert 2016-09-02, database saves it as 2016-09-01. Here is my mapping:

@DateTimeFormat(pattern="dd/MM/yyyy") 
@Column(name = "OrderDate", nullable = false)
@Type(type="org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
private final LocalDate orderDate;

I create instances by new LocalDate() I tried printing the value on console right before it gets saved into database and it prints it correctly, but the value in database is from the day before :/ So the problem is either with hibernate mapping, or with mysql. I save the date into database in this method:

   @Autowired
    private SessionFactory sessionFactory;
   public void persist(T entity) {
        getSession().persist(entity);
    }

I don't even know if it's a problem with hibernate, or mysql. This is really weird. Can you give me any pointers what could be wrong? Thanks in advance The version of mysql im using: 5.7.14-log

Edit: when i manually insert data in mysql like this:

insert into Orders values (149,14,'2016-09-03','2016-09-03',199.99)

the date is saved correctly

My sql settings: http://pastebin.com/87d6pkmE

I enabled logging in mysql, and code processed by mysql is:

2016-09-03T19:54:06.016215Z   125 Connect   root@localhost on ElectronicsStoreDB using TCP/IP
2016-09-03T19:54:06.017215Z   125 Query /* mysql-connector-java-6.0.3 ( Revision: 9fb85a76ccb7506157e668f1516464a46e317d4a ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2016-09-03T19:54:06.018215Z   125 Query SET character_set_results = NULL
2016-09-03T19:54:06.018215Z   125 Query SET autocommit=1
2016-09-03T19:54:06.018215Z   125 Query SET autocommit=0
2016-09-03T19:54:06.021216Z   125 Query select product0_.ProductID as ProductI1_4_0_, product0_.Category as Category2_4_0_, product0_.Description as Descript3_4_0_, product0_.Discontinued as Disconti4_4_0_, product0_.Manufacturer as Manufact5_4_0_, product0_.Name as Name6_4_0_, product0_.UnitPrice as UnitPric7_4_0_, product0_.UnitsInStock as UnitsInS8_4_0_ from Products product0_ where product0_.ProductID=19
2016-09-03T19:54:06.045217Z   125 Query insert into Orders (CustomerID, OrderDate, ShippingDate, TotalPrice) values (14, '2016-09-02', '2016-09-02', 437.99)
2016-09-03T19:54:06.048217Z   125 Query insert into OrderDetails (OrderID, ProductID, Quantity, UnitPrice) values (163, 19, 1, 437.99)
2016-09-03T19:54:06.091220Z   125 Query update Products set Category='Printer', Description='USB 2.0, Wi-Fi', Discontinued=0, Manufacturer='EPSON', Name='XP-610', UnitPrice=437.99, UnitsInStock=134 where ProductID=19
2016-09-03T19:54:06.092220Z   125 Query commit
2016-09-03T19:54:06.094220Z   125 Query SET autocommit=1
2016-09-03T19:54:06.095220Z   125 Query select @@session.tx_read_only
2016-09-03T19:54:06.095220Z   125 Quit  

so its the wrong date

found similar thread but it doesn't really help: dates consistently two days off i'm using jdbc.driverClassName=com.mysql.cj.jdbc.Driver


Answer:

Alright, I managed to fix this I changed jdbc url from:

jdbc:mysql://localhost:3306/ElectronicsStoreDB?useUnicode=tr‌​ue&useJDBCCompliantT‌​imezoneShift=true&us‌​eLegacyDatetimeCode=‌​false&serverTimezone‌​=UTC

to

jdbc:mysql://localhost:3306/ElectronicsStoreDB?useTimezone=trueuseUnicode=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Warsaw

And suprisingly it works as it should now

Question:

I have a Java application which uses a MyBatis TypeHandler class. It needs to make sure that the date being stored into the MySql database is in MST time.

Before any data gets to the app, we have another TypeHandler that takes the date from the database, which is in MST, and converts it to UTC. So, for example, in the database if the timestamp was:

2016-05-05 00:01:00

when the date appears on the app side it is in the following format (UTC):

2016-05-05T07:01:00.000Z

The app side does all date comparisons in UTC, but unfortunately, the MySql server must store in MST.

In order to keep dates consistent from whichever server the app is running (it is run in MST, PST as well as EST) we will need the two TypeHandlers, one to marshall the date coming into the app and one to make sure it's in MST going back.

The setParameter method of the UtcToMstDateTimeTypeHanlder:

@Override
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
{
    if (parameter != null)
    {
        //1.  2016-05-05 00:01:00 (timestamp) converted to UTC DateTime -> 2016-05-05T07:01:00.000Z
        DateTime thisDateTime = (DateTime) parameter;
        //2.  UTC DateTime converted to LocalDateTime -> 2016-05-05T07:01:00.000
        LocalDateTime thisLocalDateTime = thisDateTime.toLocalDateTime();
        //3.  LocalDateTime to MST DateTime -> 2016-05-05T07:01:00.000-07:00
        DateTime mstTime = thisLocalDateTime.toDateTime(DateTimeZone.forID("MST"));
        //4.  But TimeStamp adds 3 hours... Why?  2016-05-05 10:01:00.0
        Timestamp mstTimeStamp = new Timestamp((mstTime).getMillis());

        ps.setTimestamp(i, mstTimeStamp);
    }
    else
    {
        ps.setTimestamp(i, null);
    }
}

The TimeStamp ends up being 3 hours ahead of UTC:

2016-05-05 10:01:00.0

Not only that, but it is also more relative to UTC than MST, except now +10:01 hours ahead of UTC.

The desired effect is to have the TypeHandler write the date back to the database as the following TimeStamp:

2016-05-05 00:01:00.0

I would simply like to have the date provided back to the database (the timestamp above) to be the same as what it came out as.

Note that right now I'm running this on the United States east coast (EST).


Answer:

Here's what I ended up having to do. Note below the hard-coding of the date that I was working with, coming in from the UTC to MST typehandler. The basic idea is to get the offset from the time zone.

From the DateTimeZone JodaTime Class:

getOffset(long instant) Gets the millisecond offset to add to UTC to get local time.

        DateTime thisDateTime = new DateTime("2016-05-07T07:01:00.000Z");

        thisDateTime = thisDateTime.withZone(DateTimeZone.UTC);

        thisDateTime = thisDateTime.toLocalDateTime().toDateTime();

        DateTime mstTime = thisDateTime.withZone(DateTimeZone.forID("MST"));    

        int offset = mstTime.getZone().getOffset(new DateTime().getMillis());

        Timestamp mstTimeStamp = new Timestamp(mstTime.getMillis() + offset); // -25200000 == 7 hours

        ps.setTimestamp(i, mstTimeStamp); //2016-05-07 00:01:00

Now, no matter where the server running the app code is, the date is always entered correctly into the database, which runs in MST time.

Question:

I am having a problem using the ResultSet.getDate() method. I have a date field in MySQL and when I try to get the value, the date obtained is today's date instead of the date in the table specified. I don't know what is causing this error, I have searched other posts, but other errors with getDate() were different, like parsing or data mismatch errors or other kinds of errors. It could be an error due to time zone, because the values of the dates are from yesterday, but there's one row with date of two days ago and it's also returning today's date.

Here's the code:

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.joda.time.LocalDate;

import model.Paciente;
import teste.ConnectionFactory;

public class PacienteDao {


    // a conexão com o banco de dados
    private Connection connection;

    public PacienteDao() {
        this.connection = new ConnectionFactory().getConnection();
    }

    public void adiciona(Paciente paciente) {
        String sql = "insert into paciente" +
                " (nome_paciente,cpf_paciente,rg_paciente,data_nasc)" +

                "values (?,?,?,?)";

        try {
            PreparedStatement stmt = connection.prepareStatement(sql);

            stmt.setString(1, paciente.getNome_paciente());
            stmt.setString(2, paciente.getCpf());
            stmt.setString(3, paciente.getRg());

            java.sql.Date data_nasc = new java.sql.Date(paciente.getData_nasc().toDate().getTime());
            stmt.setDate(4, data_nasc);

            stmt.execute();
            stmt.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Paciente> listaPacientes() {

        List<Paciente> pacientes = new ArrayList<Paciente>();

        try {
            PreparedStatement stmt = this.connection.prepareStatement("select * from paciente");

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Paciente paciente = new Paciente();
                paciente.setId_paciente(rs.getInt("id_paciente"));
                paciente.setNome_paciente(rs.getString("nome_paciente"));
                paciente.setCpf(rs.getString("cpf_paciente"));
                paciente.setRg(rs.getString("rg_paciente"));

                LocalDate dt = new LocalDate();
                dt.fromDateFields(rs.getDate("data_nasc"));
                paciente.setData_nasc(dt);

                pacientes.add(paciente);
            }

            rs.close();
            stmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return pacientes;
    }

Here's the data that should be returned (CSV):

  • "1","Lucas","1111111111","12222222","2017-12-19"
  • "2","Lucas","1111111111","12222222","2017-12-20"
  • "3","Lucas","1111111111","12222222","2017-12-20"
  • "4","Leandro","2321","21232","2017-12-20"

Here's the data that is been returned (StackTrace):

Id: 1 Nome: Lucas CPF: 1111111111 RG: 12222222 Data de Nascimento: 2017-12-21

Id: 2 Nome: Lucas CPF: 1111111111 RG: 12222222 Data de Nascimento: 2017-12-21

Id: 3 Nome: Lucas CPF: 1111111111 RG: 12222222 Data de Nascimento: 2017-12-21

Id: 4 Nome: Leandro CPF: 2321 RG: 21232 Data de Nascimento: 2017-12-21

Like I said one of the rows has a date of two days ago, but it's showing today's date too, so I think isn't a time zone error.

PS: The name of the variables and methods are in Portuguese, because the application is in Portuguese too.


Answer:

The problem is here

LocalDate dt = new LocalDate();
dt.fromDateFields(rs.getDate("data_nasc"));

The first statement creates a new LocalDate set to today. The second statement is a call to the static method fromDateFields, which should have been flagged as a warning by your IDE an/or compiler. This method returns a new LocalDate object, which you discarded, and does not modify dt. The above should be:

LocalDate dt = LocalDate.fromDateFields(rs.getDate("data_nasc"));

Question:

I have a table in my MySQL database that has a date column:

+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| id                | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| type              | varchar(50)   | NO   |     | NULL    |                |
| expiration        | date          | NO   |     | NULL    |                |

I'm using MySQL with JPA to save dates. I have a feature that the user can select a final date range and it will get all the dates.

Check out this code (with a bunch of SYSOs) to try to see whats going on...

@Override
    protected DateTime nextReference(DateTime reference) {
        System.out.println("Reference: " + reference.toString("dd-MM-YYYY"));
        DateTime plus = reference.plusMonths(1);
        System.out.println("One month from now: " + plus.toString("dd-MM-YYYY"));

        DateTime result = plus.withDayOfMonth(reference.getDayOfMonth());
        System.out.println("Final: " + result.toString("dd-MM-YYYY"));

        return result;
    }

This part, the result was fine:

Reference: 10-01-2017
One month from now: 10-02-2017
Final: 10-02-2017
Reference: 10-02-2017
One month from now: 10-03-2017
Final: 10-03-2017
Reference: 10-03-2017
One month from now: 10-04-2017
Final: 10-04-2017
Reference: 10-04-2017
One month from now: 10-05-2017
Final: 10-05-2017
Reference: 10-05-2017
One month from now: 10-06-2017
Final: 10-06-2017
Reference: 10-06-2017
One month from now: 10-07-2017
Final: 10-07-2017
Reference: 10-07-2017
One month from now: 10-08-2017
Final: 10-08-2017
Reference: 10-08-2017
One month from now: 10-09-2017
Final: 10-09-2017
Reference: 10-09-2017
One month from now: 10-10-2017
Final: 10-10-2017
Reference: 10-10-2017
One month from now: 10-11-2017
Final: 10-11-2017
Reference: 10-11-2017
One month from now: 10-12-2017
Final: 10-12-2017
Reference: 10-12-2017
One month from now: 10-01-2018
Final: 10-01-2018

Ok, now lets move to the save part:

@Transactional
private void saveTransactions(List<Transaction> transactions) {
    for (Transaction t : transactions) {
        System.out.println("Saving: " + t.getExpiration().toString("dd-MM-YYYY"));
        Transaction saved = dao.save(t);
        System.out.println("Saved: " + saved.getExpiration().toString("dd-MM-YYYY"));
    }
}

As you can see, I put some lines to debug this also.... Before I continue with the output, check out the DAO:

public T save(T entity) {
    entityManager.persist(entity);
    return entity;
}

No big deal right... The output:

Saving: 10-02-2017
Saved: 10-02-2017
Saving: 10-03-2017
Saved: 10-03-2017
Saving: 10-04-2017
Saved: 10-04-2017
Saving: 10-05-2017
Saved: 10-05-2017
Saving: 10-06-2017
Saved: 10-06-2017
Saving: 10-07-2017
Saved: 10-07-2017
Saving: 10-08-2017
Saved: 10-08-2017
Saving: 10-09-2017
Saved: 10-09-2017
Saving: 10-10-2017
Saved: 10-10-2017
Saving: 10-11-2017
Saved: 10-11-2017
Saving: 10-12-2017
Saved: 10-12-2017

As you can see... It should be fine right? Everything on the 10th.

Before I continue again, check the model, and the converter:

    //Attribute
    @Convert(converter = JpaDateConverter.class)
    private DateTime expiration;

//Converter

public class JpaDateConverter implements AttributeConverter<DateTime, Date> {

    @Override
    public Date convertToDatabaseColumn(DateTime objectValue) {
        return objectValue == null ? null : new Date(objectValue.getMillis());
    }

    @Override
    public DateTime convertToEntityAttribute(Date dataValue) {
        return dataValue == null ? null : new DateTime(dataValue);
    }

}

Now look at my database:

mysql> select expiration from tb_transaction where notes = 3 and year(expiration
) = 2017;
+------------+
| expiration |
+------------+
| 2017-01-10 |
| 2017-02-10 |
| 2017-03-09 |
| 2017-04-09 |
| 2017-05-09 |
| 2017-06-09 |
| 2017-07-09 |
| 2017-08-09 |
| 2017-09-09 |
| 2017-10-09 |
| 2017-11-10 |
| 2017-12-10 |
+------------+
12 rows in set (0.00 sec)

For some, wierd, mystic reason, some dates where saved on the 9ths instead of the 10th !!

No Warnings, no Errors on MySQL driver or nothing.

Please HELP guys!

EDIT Transaction Class:

@Entity
@Table(name = "tb_transaction")
public class Transaction implements Cloneable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Enumerated(STRING)
    private TransactionType type;

    @Convert(converter = JpaDateConverter.class)
    private DateTime expiration;

Answer:

Thanks to @RickS, we solved this issue.

PS: I solved this a long time ago, and experienced this issue again 2 years later. lol

The solution is in the converter: JpaDateConverter, when you convert a DateTime Objetivo to java.sql.Date, according to the docs:

If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

So to fix this I changed the server timezone:

dpkg-reconfigure tzdata

And in MySQL:

SET @@global.time_zone = '+00:00';

Question:

I really don't have an idea on what is wrong with my code. I am using Joda-Time api on this case. This code is after rs.next(), I need to get the value of hh in integer because i have to multiply it on the hourly rate of a parking service.

java.sql.Time timeOut = new java.sql.Time(new java.util.Date().getTime());
java.sql.Time timeIn = rs.getTime("timeIn");
DateTime d2 = new DateTime(timeOut);//Value is 18:39:24
DateTime d1 = new DateTime(timeIn);//Value is 16:03:50
int hh = Hours.hoursBetween(d1,d2).getHours();
txtDuration.setText(""+hh);//Display on textbox and shows 421946

also tried this

Duration duration = new Duration(d1,d2);
long ss = duration.getStandardSeconds();
long mm = ss/60;
long hh = mm/60;

Good day and Thank you in advance.


Answer:

From java.sql.Time javadoc

The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed.

And then, in your code

java.sql.Time timeOut = new java.sql.Time(new java.util.Date().getTime());

new java.util.Date() in general is not going to be January 1, 1970, so you are in fact creating an invalid java.sql.Time instance. What is probably happening under the hood is that you are calculating the number of hours between some moment in January 1 1970 (your timeIn, that is coming from the database and can be assumed to be correct) and some moment right now:

421946 hours = 421946 / 24 days ~ 17581 days ~ 17581 / 365 years ~ 48 years.

And 1970 + 48 = 2018, this year.