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
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!!!!!
On Wed, Aug 21, 2013 at 10:39 PM, 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!!!!!
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
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 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
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.