List:General Discussion« Previous MessageNext Message »
From:Nick Cameo Date:August 22 2013 6:22pm
Subject:Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
View as plain text  
On Wed, Aug 21, 2013 at 10:39 PM, <hsv@stripped> wrote:

> >>>> 2013/08/21 18:03 -0400, Nick Khamis >>>>
> 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
> <<<<<<<<
> 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("yyyy-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.

Thread
Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!Nick Khamis21 Aug
  • Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!Michael Dykman21 Aug
  • Re: Java UTC Calendar and Mysql TimeStamp - Gets me everytime!!!!!hsv22 Aug
    • Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!Nick Cameo22 Aug
      • Re: Java UTC Calendar and Mysql TimeStamp - Gets me everytime!!!!!hsv23 Aug
    • Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!Nick Cameo22 Aug