0

So I read here : Why doesn't MySQL support millisecond / microsecond precision? that from the version 5.6.4, the datetime format should take until milliseconds.

But it doesn't work for me even for deciseconds it doesn't take. When I do my "INSERT" query, the value saved looks like this "2014-09-15 15:05:54".

The value I wanted to insert look like this : "2014-09-15T15:05:54.0" So when I do comparaison, I get errors :/

Here is my INSERT Query :

            $bdd->query('INSERT INTO ev
            (id_event, ts, sp, lg, dir)
            VALUES 
            (NULL,"'.$evTs.'","'.$ev_sp.'","'.$ev_lg.'","'.$ev_dir.'")');

Where "$evTs" is the timestamp.

Update 1

How I create my table ev :

'CREATE TABLE `ev` (
`id_event` int(32) NOT NULL AUTO_INCREMENT,
`ts` datetime NOT NULL,
`sp` int(32) NOT NULL,
`lg` int(32) NOT NULL,
`dir` int(32) NOT NULL,
`nbr_voie` int(32) NOT NULL,
PRIMARY KEY (`id_event`)

)

Community
  • 1
  • 1
Booba__2012
  • 143
  • 1
  • 3
  • 14
  • 1
    Please show us the `CREATE TABLE` statement of your `ev` table. You get this i.e. with `SHOW CREATE TABLE ev`. It's got to be DATETIME(3) to get milliseconds. – VMai Sep 25 '14 at 14:35
  • Two possible reasons: 1) A timestamp in PHP is an integer in whole seconds, so maybe you store the wrong value. 2. The result you get might be formatted in a way that hides milliseconds. It would help if you could show the final query. These variables are not very helpful. – GolezTrol Sep 25 '14 at 14:36
  • You've got to use `ALTER COLUMN` to get [fractional second support](http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html), as I said. Then use the syntax as mentioned by @MarcB. – VMai Sep 25 '14 at 14:40

1 Answers1

2

That's because T is not part of a valid mysql timestring. MySQL's date/time string format has always been

yyyy-mm-dd hh:mm:ss

and now that fractional seconds are supported

yyyy-mm-dd hh:mm:ss.sssss

Nowhere in that format is there ANY support for T. You'll have to use a STR_TO_DATE() to parse your "non-standard" format into something MySQL will accept. Or even simply just use a string replacement and do REPLACE('yourstring', 'T', ' ')

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Yes, I noticed the "T" too but when I did the "INSERT" I had it in the correct format till the seconds. Ok, I will try your answer tomorrow. Thanks. – Booba__2012 Sep 25 '14 at 14:43