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.
pstmt.setTimestamp(1, /* some expression converting Time.get(q) into a java.sql.Timestamp object */);
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.
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.