Bug #54561 Custom Prompt formatting tags
Submitted: 16 Jun 2010 19:01 Modified: 18 Jun 2010 17:01
Reporter: Sean Kelly Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version: OS:Linux
Assigned to: CPU Architecture:Any

[16 Jun 2010 19:01] Sean Kelly
Description:
Setting up a custom prompt in Mysql client as:

> prompt \Y-\o-?? \R:\m:\s [\d] >
PROMPT set to '\Y-\o-?? \R:\m:\s [\d] >'
2010-6-?? 11:46:10 [mtp] >

The formatting options allow selection of year, month, hour, minute, second... but not day (or if it does, it's not documented). I wanted my prompt to show the current timestamp in the same format that would allow copy & paste for use query conditions without having to do a "select now();" query first.

How to repeat:
You can't repeat something that doesn't exist - that's the definition of a feature request... silly question :^)

Suggested fix:
Add all the date time formatting options used by date_format()!
[18 Jun 2010 16:56] Valeriy Kravchuk
Indeed, no documented way (http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html) to do this.
[18 Jun 2010 17:01] Sean Kelly
If you spent three seconds to READ what you linked vs. what I wrote instead of being "smart" about it, you would find the formatting options do NOT include day of month:

The string for defining the prompt can contain the following special sequences:

Option 	Description
\c 	A counter that increments for each statement you issue
\D 	The full current date
\d 	The default database
\h 	The server host
\l 	The current delimiter (new in 5.0.25)
\m 	Minutes of the current time
\n 	A newline character
\O 	The current month in three-letter format (Jan, Feb, …)
\o 	The current month in numeric format
\P 	am/pm
\p 	The current TCP/IP port or socket file
\R 	The current time, in 24-hour military time (0–23)
\r 	The current time, standard 12-hour time (1–12)
\S 	Semicolon
\s 	Seconds of the current time
\t 	A tab character
\U 	Your full user_name@host_name account name
\u 	Your user name
\v 	The server version
\w 	The current day of the week in three-letter format (Mon, Tue, …)
\Y 	The current year, four digits
\y 	The current year, two digits
\_ 	A space
\  	A space (a space follows the backslash)
\' 	Single quote
\" 	Double quote
\\ 	A literal “\” backslash character
\x 	x, for any “x” not listed above
[8 Jul 2011 21:18] Dimitriy A
I was also trying to add a custom date to the prompt, since the default format does not fit my needs.

The default date looks like this: Fri Jul  8 14:08:16 2011

I want to format it like the following: YYYY-MM-DD hh:mm:ss TZD
So I have something like: 2011-07-08 14:08:16 PDT
Where:
     YYYY = four-digit year
     MM   = two-digit month (01 through 12)
     DD   = two-digit day of month (01 through 31)
     hh   = two digits of hour (00 through 23)
     mm   = two digits of minute (00 through 59)
     ss   = two digits of second (00 through 59)
     TZD  = time zone designator

MySQL prompt seems to lack a two digit month designator. It does not have any kind of day designator. And it lacks time zone designator.
[25 Sep 2013 19:01] Dennis Allard
For the MySQL prompt command...

Wow, no way to specify a data format of the form YYYY-MM-DD HH24:MI.

The \o format spec outputs a one digit for months less than 10.

There is no format spec for the day of the month (much less a two digit day of the month).

Example, how specify a format that would output today as 2013-09-24 11:59 US/Pacific, which is when I am entering this comment.

It is very useful to have dates output as YYYY-MM-DD HH24:MI since one can easily sort ones output by date, for example.

Dennis Allard
http://oceanpark.com