Bug #77142 prompt command is mssing format specifiers for two digit month and two digit day
Submitted: 23 May 2015 12:58 Modified: 17 Jul 2015 12:50
Reporter: Dennis Allard Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[23 May 2015 12:58] Dennis Allard
Description:
The prompt date formats are lacking.  No way to output a date in the prompt of the from YYYY-MM-DD HH24:MM.

I like to show the current date and time in my prompts.

In this way, I can record an entire session and see when I did something.  Since I live in emacs and run command shells, I am able to record entire sessions in Linux and Oracle using date formats in my prompts that look as follows (I show both my Linux command line and Oracle command line prompts):

=======================================================

[2015-05-23 05:35 allard@bamboo ~]$ 
[2015-05-23 05:35 allard@bamboo ~]$ date
Sat May 23 05:35:47 PDT 2015
[2015-05-23 05:35 allard@bamboo ~]$ sqlplus dennis/dennis@xe

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 23 05:35:53 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

[2015-05-23 05:35:53 DENNIS@xe] SQL> select sysdate from dual;

SYSDATE
-------------------
2015-05-23 05:36:06

1 row selected.

[2015-05-23 05:36:06 DENNIS@xe] SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[2015-05-23 05:36 allard@bamboo ~]$ 

=======================================================

However, the best I can do (as of this writing) in MySQL is:

=======================================================

[2015-05-23 05:36 allard@bamboo ~]$ 
[2015-05-23 05:36 allard@bamboo ~]$ sudo mysql -u... -p...
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 135067
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[Sat May 23 05:38:07 2015 opa@(none)] MYSQL> 
[Sat May 23 05:38:42 2015 opa@(none)] MYSQL> exit
Bye
[2015-05-23 05:38 allard@bamboo ~]$ 

=======================================================

I achieve the above MySQL prompt via the following section in /etc/my.cnf:

[mysql]
prompt=[\\D \\u@\\d] MYSQL>\\_

If you look in the docs for the prompt command, there seems to be no way to create a format string that shows the current month as two digits (including a leading zero) and no way to show the current day of the month (including a leading zero).  Hence, no way to create a prompt that includes YYYY-MM-YY HH24:MM (to use Oracle parlance and as shown above).

To summarize, MySQL should add two additional prompt formats to show the current month and the current day, both as two digits (including leading zeros).
 

How to repeat:
Try to create a MySQL command line prompt that contains the current date and time in the form: YYYY-MM-DD HH24:MI:SS

You will not be able to do so due to lack of a complete set of formatting directives for the prompt command.

Suggested fix:
Add two new formatting directives to the prompt command that output the current month and the current day of month.
[24 May 2015 7:16] Shane Bester
also https://bugs.mysql.com/bug.php?id=76854
[17 Jul 2015 12:50] Georgi Kodinov
Thank you for you reasonable feature request !
[17 Jul 2015 13:01] Georgi Kodinov
FYI, it looks like we have the month, it's "\o". See http://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html for details. But I agree we are lacking the day of the month.
Your best workaround is indeed "\D" that formats the date/time according to the client OS locale. And then manipulating the LC_TIME (if you're on linux).