Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with adding time

 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have written a Java program that reads data from an Excel file and puts it into a MySQL database.
Everything works fine, but I have a small problem.

In excel the date and time is written like this: '25/04/2013 00:00:00'.
The column 'Time' in MySQL is VARCHAR(45), because Eclipse gave errors when I had chosen the DATETIME type.
The program stores the date and time in an ArrayList. From that ArrayList the database is filled.
When the program is done, the MySQL Time column only shows '25-apr-2013' without the time. How is this possible and how can I fix it?

Thanks in advance!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch, Jil!

Somewhere in your code the date is transformed from a date/time representation (such as GregorianCalendar) to a String to be stored in the database. This conversion results in the format you ultimately see in the database. It should be possible to change the format string of the conversion to whatever format you prefer.

However, storing dates as texts (VARCHAR) in the database is not a good practice. The format in which the date is stored is not enforced (as you're experiencing now), but there are other downsides. I would advise to declare the field as DATETIME and try to resolve the problems connected with that. You can ask here if you have problems getting the DATETIME datatype work. If you do, don't forget to include the error message (stack trace) and relevant code in your question.
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oops, my internet posted the reply twice. My response and problem is below this post.
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:Welcome to the Ranch, Jil!

Somewhere in your code the date is transformed from a date/time representation (such as GregorianCalendar) to a String to be stored in the database. This conversion results in the format you ultimately see in the database. It should be possible to change the format string of the conversion to whatever format you prefer.

However, storing dates as texts (VARCHAR) in the database is not a good practice. The format in which the date is stored is not enforced (as you're experiencing now), but there are other downsides. I would advise to declare the field as DATETIME and try to resolve the problems connected with that. You can ask here if you have problems getting the DATETIME datatype work. If you do, don't forget to include the error message (stack trace) and relevant code in your question.


Thank you for the advice!
I can't figure out how to make the DATETIME type to work.
Here is my code and error message:

Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '25-apr-2013' for column 'Time' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2983)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at ReadMeteredEnergy4.main(ReadMeteredEnergy4.java:143)

Relevant code where I read from the Excel file:


This is how the schema and table is created:


Relevant code where I write to the database:

 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would be better off using PreparedStatement the way it was meant to be used. So instead of this:



try something like this:


>
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, I changed the suggestions you made for the PreparedStatements, but I don't find an expression for converting to Timestamp.

pstmt.setTimestamp(1, /* some expression converting Time.get(q) into a java.sql.Timestamp object */);


I tried , but that doesn't seem to do the trick because in Excel it isn't written like yyyy-mm-dd hh:mm:ss.[ffffffff].
Any other suggestions than Timestamp or how I can make Timestamp work?

Another thing I find annoying is that

doesn't work, but

works. But in my opinion that's a lot of code for such a simple demand.
Why isn't the (double) working?

Any suggestions?
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyone?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the type of the consumption variable?

I guess it is some representation of the Excel value, depending on the library you're using to read data from Excel. You should be able to use some method to obtain the value of the Excel cell as Double (and not as String). Your current approach is that you're parsing the textual value of the Excel cell, which might be affected by any special format applied to the cell (eg. rounding) and might fail under some circumstances (eg. Excel using dot as a decimal separator, while Java uses comma - I've seen such situations).

If you want to avoid using repeated code, you might create your own helper methods, which would accept a prepared statement, a parameter index and the Excel cell representation and took care of extracting Double from the cell and setting it to the prepared statement. Even better would be (in my opinion) converting Excel cells to Java native types as soon as they are read from the sheet and not manipulating Excel cell representations in the database code at all, thus separating the Excel-specific code from the database-specific code entirely.

The same would apply for extracting time from Excel sheet - you must find a better way than interpreting the textual value of the cell. Excel stores date/time as a Double internally, counting number of days since some start of an epoch (I believe zero corresponds to 31.12.1899) - if your library doesn't provide a better method, you might be forced to read it as Double and compute the real date from it. Handling time zones can be tricky - you should probably always treat Excel date/time in the local time zone.
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:What is the type of the consumption variable?

I guess it is some representation of the Excel value, depending on the library you're using to read data from Excel. You should be able to use some method to obtain the value of the Excel cell as Double (and not as String). Your current approach is that you're parsing the textual value of the Excel cell, which might be affected by any special format applied to the cell (eg. rounding) and might fail under some circumstances (eg. Excel using dot as a decimal separator, while Java uses comma - I've seen such situations).

If you want to avoid using repeated code, you might create your own helper methods, which would accept a prepared statement, a parameter index and the Excel cell representation and took care of extracting Double from the cell and setting it to the prepared statement. Even better would be (in my opinion) converting Excel cells to Java native types as soon as they are read from the sheet and not manipulating Excel cell representations in the database code at all, thus separating the Excel-specific code from the database-specific code entirely.

The same would apply for extracting time from Excel sheet - you must find a better way than interpreting the textual value of the cell. Excel stores date/time as a Double internally, counting number of days since some start of an epoch (I believe zero corresponds to 31.12.1899) - if your library doesn't provide a better method, you might be forced to read it as Double and compute the real date from it. Handling time zones can be tricky - you should probably always treat Excel date/time in the local time zone.


Thanks for the help. Now when I read from the Excel file it is converted to the right type from the beginning.
I have read the time as a double and that works fine, but how can I change this double to datetime in mysql through java?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do I understand it right that you don't have any problem with reading floating-point values from Excel and putting them into the database (the consumption variable in your earlier posts)?

As per converting Excel's double date/time value into a Java representation: I've already mentioned that Excel stores date/time as a number of days since some arbitrary time in the past (in my opinion, it would be best if you determine this on your own, so that you'll better understand the problem). Java uses a convention in which the date/time is stored as a whole number (a long) of milliseconds since the start of Unix epoch, which is documented to be January 1, 1970 UTC (see the docs). So, to convert the Excel date into a Java date you need to multiply it by number of milliseconds in a day (24 * 60 * 60 * 1000) and then subtract the difference (in milliseconds) between the start of Excel's epoch and Java's epoch. The value you obtain in this way will probably be in your local date/time, so you then need to correctly convert that into the timezone you want to put into the database.
 
Jil Van Wetter
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:Do I understand it right that you don't have any problem with reading floating-point values from Excel and putting them into the database (the consumption variable in your earlier posts)?

As per converting Excel's double date/time value into a Java representation: I've already mentioned that Excel stores date/time as a number of days since some arbitrary time in the past (in my opinion, it would be best if you determine this on your own, so that you'll better understand the problem). Java uses a convention in which the date/time is stored as a whole number (a long) of milliseconds since the start of Unix epoch, which is documented to be January 1, 1970 UTC (see the docs). So, to convert the Excel date into a Java date you need to multiply it by number of milliseconds in a day (24 * 60 * 60 * 1000) and then subtract the difference (in milliseconds) between the start of Excel's epoch and Java's epoch. The value you obtain in this way will probably be in your local date/time, so you then need to correctly convert that into the timezone you want to put into the database.


No, I don't have any problem with the floatin-point values.
I will try what you said about the date and time.
Thanks!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic