top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

+1 vote
417 views

We have the following mysql timetampe field

startdate | timestamp | NO | | 0000-00-00 00:00:00

When trying to insert a long value in there:

Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
c.getTimeInMillis();

We are presented with the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1377119243640' for column 'stopdate' at row 1

Our environments is:

JDBC Driver = 5.1.26
Mysql = 5.5

show variables like 'time_zone%';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +00:00 |
+---------------+--------+

SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
posted Aug 21, 2013 by Abhay Kulkarni

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote
 
Best answer
select FROM_UNIXTIME(1377196112065/1000);
+-----------------------------------+
| FROM_UNIXTIME(1377196112065/1000) |
+-----------------------------------+
| 2013-08-22 18:28:32 |
+-----------------------------------+
answer Aug 22, 2013 by Majula Joshi
+1 vote

You should have answered your own question in the text.
The MySql TIMESTAMP type is, as all other timestamps in the *nix world, a count of seconds since epoch time. The Java function you are using yields MILLI-seconds. Divide it by 1000 and you should be good to go.

answer Aug 22, 2013 by Jagan Mishra
Similar Questions
+3 votes
MySQL Java-connector on Windows?

I have MySQL 5.5.8 under Windows Vista, and I am minded to write Java programs to talk to the server. I believe that a connector is needed for that, something with ODBC in the name--which version is best for my use?

+2 votes
How to encrypt a mysql Database password?

In my web application I need to encrypt the database password and and that should be stored in the properties file?

+1 vote
How to update null into a column type Date in MySQL ?

I have a table in Mysql DB like following

mysql> describe APPTBL;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| APP_ID      | int(11)     | NO   | PRI | NULL    | auto_increment |
| CREATE_DATE | date        | YES  |     | NULL    |                |
| CLOSE_DATE  | date        | YES  |     | NULL    |                |
| ACTION      | text        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

While Starting of App, i am inserting the app details with CREATE_DATE and when it is closing, i am updating its CLOSE_DATE. But in case if user is restarting the app, i want to update NULL in place of CLOSE_DATE column. Here is my query,

mysql> update APPTBL set CLOSE_DATE = NULL where APP_ID = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select * from APPTBL ;
+-----------+-------------+------------+------------+
|   APP_ID  | CREATE_DATE | CLOSE_DATE |   ACTION   |
+-----------+-------------+------------+------------+
|    1      |  2013-10-08 | NULL       | XXXXXYYYYY |
+-----------+-------------+------------+------------+
1 row in set (0.00 sec)

But why i am not getting any result in this??

mysql> select * from APPTBL where CLOSE_DATE = NULL;
Empty set (0.00 sec)

Also, as i am writhing a java program using this where 

rs = st.executeQuery("update APPTBL set CLOSE_DATE = NULL where APP_ID = 1");
if(!rs.wasNull()){
   // Why it is coming here??
}
...