Insert and Search in Datetime field

Question

Insert and Search in Datetime field

Navigation

#1 by (2 votes)

2

I'm trying to insert a date with milliseconds, but I can not do it. The bash date has the following format:

"%d%m%Y%H%M%S%2N"

While in BBDD I see the following:

MariaDB [db]> INSERT INTO access_wifi(DATA) VALUES ('0511201813450367');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [db]> select * from access_wifi;
+----+------------------------+
| id | DATA                   |
+----+------------------------+
|  1 | 0000-00-00 00:00:00.00 |
|  2 | 0000-00-00 00:00:00.00 |
|  3 | 0000-00-00 00:00:00.00 |
+----+------------------------+
3 rows in set (0.00 sec)

What command should I use to insert and search?

mysql query mariadb

asked by Guif If 05.11.2018 в 15:47

source

1 answer

SQL query Advanced filter with Postgres how to know how many records a query returns sql server 2012 with php

score 2 · Accepted Answer

From what you say, you're trying to insert a string with a certain format in the DATA column that seems to be some kind of datetime , in which case, the problem you have is that you should "interpret" the string in a datetime before inserting the value. We can use STR_TO_DATE() :

INSERT INTO access_wifi(DATA) VALUES (STR_TO_DATE('0511201813450367', '%d%m%Y%H%i%s%f'))

Then, the other problem is to see the full date, for which you can make use of DATE_FORMAT() :

select  DATE_FORMAT(DATA, '%Y-%m-%d %H:%i:%s:%f')
        FROM access_wifi

you can consult the complete documentation of the format string in the links to the functions, in your case we are using

╔════╦════════════════════════════════════╗
║ %d ║ Day of the month, numeric (00..31) ║
╠════╬════════════════════════════════════╣
║ %f ║ Microseconds (000000..999999)      ║
╠════╬════════════════════════════════════╣
║ %H ║ Hour (00..23)                      ║
╠════╬════════════════════════════════════╣
║ %i ║ Minutes, numeric (00..59)          ║
╠════╬════════════════════════════════════╣
║ %m ║ Month, numeric (00..12)            ║
╠════╬════════════════════════════════════╣
║ %Y ║ Year, numeric, four digits         ║
╠════╬════════════════════════════════════╣
║ %s ║ Seconds (00..59)                   ║
╚════╩════════════════════════════════════╝

Note: The column should be a datetime (eg datetimr(3) ) with enough precision to accommodate the milliseconds that you want to insert