The Ultimate Guide to High Availability with MariaDB

Comments - need to load csv date data along with AM/PM values.

4 years, 9 months ago Anel Husakovic
Re: need to load csv date data along with AM/PM values.

Hi @kranthi,
any news regarding this ?

I have played a bit and basically you have to convert from datetime to string using function date_format() which returns the string.

Test %r and %p parameters on string

MariaDB [test]> select @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.000 sec)

MariaDB [test]> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); # this works -> string (used `%r`)
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                         |
+------------------------------------------------------------+

MariaDB [test]>  SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %p %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %p %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 PM 22:23:00 00 6                                  |
+------------------------------------------------------------+
1 row in set (0.000 sec)

Test on datetime type

This will not work

MariaDB [test]> set @starttime=now();
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @starttime;
+---------------------+
| @starttime          |
+---------------------+
| 2019-07-22 04:28:59 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [test]> SELECT DATE_FORMAT(@starttime, '%H %k %I %r %T %S %w');   # with @starttime doesn't work
+-------------------------------------------------+
| DATE_FORMAT(@starttime, '%H %k %I %r %T %S %w') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+

This will work: Convert datetime type to string using date_format

MariaDB [test]> SELECT DATE_FORMAT(DATE_FORMAT(@starttime, '%Y-%m-%d'), '%H %k %I %r %T %S %w');   # convert @starttime to string using date_format
+--------------------------------------------------------------------------+
| DATE_FORMAT(DATE_FORMAT(@starttime, '%Y-%m-%d'), '%H %k %I %r %T %S %w') |
+--------------------------------------------------------------------------+
| 00 0 12 12:00:00 AM 00:00:00 00 1                                        |
+--------------------------------------------------------------------------+
1 row in set (0.000 sec)

Variation: format during the conversion has to be %Y-%m-%d otherwise it will not work

MariaDB [test]> SELECT DATE_FORMAT(DATE_FORMAT(@starttime, '%d-%m-%Y'), '%H %k %I %r %T %S %w');
+--------------------------------------------------------------------------+
| DATE_FORMAT(DATE_FORMAT(@starttime, '%d-%m-%Y'), '%H %k %I %r %T %S %w') |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set, 1 warning (0.000 sec)
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.