Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
On Wed, Aug 21, 2013 at 10:39 PM, h...@tbbs.net wrote: 2013/08/21 18:03 -0400, Nick Khamis We have the following mysql timetampe field startdate | timestamp | NO | | -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 Ugh, where is the SQL? In any case, although it looks as if that is MySQL s internal TIMESTAMP representation, one does not directly use Unix timestamps; instead, one converts them with the MySQL function FROM_UNIXTIME. The same effect may be gotten with any timestamp-formatting function that yields a string in the form '2013/08/21 18:03:00' (it is all one whether the separator is hyphen, slant, colon, ...). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql What I am trying to accomplish is pass down a valid long value (representative of UTC time) that mysql timestamp field accepts, unix time epoch whatever That way, I do not have to fight with java.sql.timestamp or java.sql.Date/Calander (for the love!@E@#!@) for reads and writes. I can't use Joda until it has been included... Deep breaths What I tried is the following: Straight Date: update test set stopdate='2013-08-22T17:49:45'; - Works Fine Formatted Date (long): SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new Locale(en, US)); long qu = sdf.parse(sdf.format(c.getTime())).getTime(); update test set stopdate='1377194323000'; - Zeros Out Formatted Date (long with milliseconds): c.getTimeInMillis() update test set stopdate='1377195098956'; - Zeros Out Formatted Date (long with milliseconds/1000): c.getTimeInMillis() / 1000 update test set stopdate='1377195098.956'; Can't change the table field to bigint either, it's an already existing project. Someone please help before I fire myself :). Kind Regards, Nick.
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
Sorry, as was mentioned earlier: select FROM_UNIXTIME(1377196112065/1000); +---+ | FROM_UNIXTIME(1377196112065/1000) | +---+ | 2013-08-22 18:28:32 | +---+ Have a good day everyone :) Nick.
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
2013/08/22 14:22 -0400, Nick Cameo SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new Locale(en, US)); Well, you have your answer (FROM_UNIXTIME( /1000)), but that stupid ISO format with 'T' in the middle does not work, because to MySQL letters are not separators--which, I am sorry to say, I did not say, although it was in the back of my mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
Hello Everyone, We have the following mysql timetampe field startdate | timestamp | NO | | -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 | +---+ | | +---+ Not sure why I am getting this error. Thanks in Advance, Nick.
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
Nick, 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. On Wed, Aug 21, 2013 at 6:03 PM, Nick Khamis sym...@gmail.com wrote: Hello Everyone, We have the following mysql timetampe field startdate | timestamp | NO | | -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 | +---+ | | +---+ Not sure why I am getting this error. Thanks in Advance, Nick. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
2013/08/21 18:03 -0400, Nick Khamis We have the following mysql timetampe field startdate | timestamp | NO | | -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 Ugh, where is the SQL? In any case, although it looks as if that is MySQL s internal TIMESTAMP representation, one does not directly use Unix timestamps; instead, one converts them with the MySQL function FROM_UNIXTIME. The same effect may be gotten with any timestamp-formatting function that yields a string in the form '2013/08/21 18:03:00' (it is all one whether the separator is hyphen, slant, colon, ...). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
