top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration
Print Preview

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

+1 vote
88 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 Google+ Share Button LinkedIn Share Button Multiple Social 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

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

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

+1 vote

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??
}
+1 vote

In my web application I have a requirement database password should be stored in properties fie and that should be encrypted and during login that it needs to check the username password and database password database password should be decrypt during login and and encrypt during log out.


Useful Links with Similar Problem
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...