<<

. 124
( 132 .)



>>

Day of the month, two numeric digits (01...31)
%d

Day of the month, numeric (1...31)
%e

Month, two numeric digits (01...12)
%m

Month, numeric (1...12)
%c

Abbreviated month name (Jan...Dec)
%b

Day of year (001...366)
%j

Hour (00...23)
%H

Hour (0...23)
%k

Hour (01...12)
%h

Hour (01...12)
%I

Minutes, numeric (00...59)
%i

Time, 12-hour (hh:mm:ss [AP]M)
%r

Time, 24-hour (hh:mm:ss)
%T

Seconds (00...59)
%S

Seconds (00...59)
%s

AM or PM
%p

Day of the week (0=Sunday...6=Saturday)
%w

Week (0...53), where Sunday is the first day of the week
%U

Continued
728 Part V: Appendixes


TABLE J-2 DATE_FORMAT SPECIFIERS (Continued)

Specifier Meaning

Week (0...53), where Monday is the first day of the week
%u

Week (1...53), where Sunday is the first day of the week; used with %X
%V

Week (1...53), where Monday is the first day of the week; used with %x
%v

Year for the week, where Sunday is the first day of the week; numeric,
%X
four digits, used with %V
Year for the week, where Monday is the first day of the week; numeric,
%x
four digits, used with %v
A literal %
%%




All other characters are just copied to the result without interpretation:

mysql> select date_format(˜2001-01-01™, ˜%W %M %d, %Y™);
+-------------------------------------------+
| date_format(˜2001-01-01™, ˜%W %M %d, %Y™) |
+-------------------------------------------+
| Monday January 01, 2001 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(˜2001-01-01 15:30:20™,
->™%W %M %d, %Y %I:%i:%S %p™);
+----------------------------------------------------------------+
| date_format(˜2001-01-01 15:30:20™, ˜%W %M %d, %Y %I:%i:%S %p™) |
+----------------------------------------------------------------+
| Monday January 01, 2001 03:30:20 PM |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

For MySQL 3.23 and later versions, the % character is required before format-
specifier characters. In earlier versions of MySQL, % was optional.

TIME_FORMAT
This function is used like the DATE_FORMAT() function just discussed, but the format
string can contain only those format specifiers that handle hours, minutes, and
Appendix J: MySQL Function Reference 729

seconds. If specifiers other than those for hours, minutes, and seconds are included,
the function will return a NULL value.

TIME_FORMAT(time,format) (used in examples)
RETURNS: string


CURDATE
This function returns today™s date as a value in YYYY-MM-DD or YYYYMMDD format,
depending on whether the function is used in a string or a numeric context:

CURDATE() or CURRENT_DATE (used in examples)
RETURNS: mixed


CURTIME
This function returns the current time as a value in HH:MM:SS or HHMMSS format,
depending on whether the function is used in a string or a numeric context:

CURTIME() or CURRENT_TIME
RETURNS: mixed


NOW
This function returns the current date and time as a value in YYYY-MM-DD HH:MM:SS
or YYYYMMDDHHMMSS format, depending on whether the function is used in a string
or a numeric context:

NOW()

or

SYSDATE()

or

CURRENT_TIMESTAMP (used in examples)
RETURNS: string


UNIX_TIMESTAMP
If this function is called with no argument, it returns a Unix timestamp (seconds
since 1970-01-01 00:00:00 GMT). If UNIX_TIMESTAMP() is called with a date argu-
ment, it returns the value of the argument as seconds since 1970-01-01 00:00:00
GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in
the format YYMMDD or YYYYMMDD in local time.
730 Part V: Appendixes

UNIX_TIMESTAMP([date])
RETURNS: int


FROM_UNIXTIME
This function returns a representation of the unix_timestamp argument as a value
in “YYYY-MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS” format, depending on whether
the function is used in a string or numeric context:

FROM_UNIXTIME(unix_timestamp) (used in examples)
RETURNS: string


SEC_TO_TIME
This function returns the seconds argument, converted to hours, minutes, and sec-
onds, as a value in HH:MM:SS or HHMMSS format, depending on whether the function
is used in a string or numeric context:

SEC_TO_TIME(seconds)
RETURNS: string


TIME_TO_SEC
This function returns the time argument, converted to seconds:

TIME_TO_SEC(time) (used in examples)
RETURNS: int

The date portion of a supplied date/time value is discarded.



Miscellaneous Functions
Here are a few other functions that don™t fall under any of the previous categories.

DATABASE
This function returns the current database name. If no current database exists,
DATABASE() returns the empty string.

DATABASE()
RETURNS: string
Appendix J: MySQL Function Reference 731


USER
This function returns the current MySQL username. In MySQL 3.22.11 or later, it
includes the client host name as well.

USER()

or

SYSTEM_USER()

or

SESSION_USER() (used in examples)
RETURNS: string


VERSION
This function returns a string indicating the MySQL server version:

VERSION()
RETURNS: string


PASSWORD
This function calculates a password string from the plain-text password str:

PASSWORD(str) (used in examples)
RETURNS: string

This is the function that encrypts MySQL passwords for storage in the Password
column of the user table. PASSWORD() encryption is one-way. PASSWORD() does not
perform password encryption in the same way in which Unix passwords are
encrypted. You should not assume that if your Unix password and your MySQL
password are the same, PASSWORD() will result in the same encrypted value that is
stored in the Unix password file. See ENCRYPT().

ENCRYPT
This function encrypts str using the Unix crypt() system call:

ENCRYPT(str[,salt])
RETURNS: string
732 Part V: Appendixes

The salt argument should be a string with two characters. (As of MySQL
3.22.16, salt may be longer than two characters.) If crypt() is not available on
your system, ENCRYPT() always returns NULL. ENCRYPT() ignores all but the first
eight characters of str on most systems.

ENCODE
This function encrypts str using pass_str as the password:

ENCODE(str,pass_str)
RETURNS: binary string

To decrypt the result, use DECODE(). The result is a binary string. If you want to
save it in a column, use a BLOB column type.

DECODE
This function decrypts the encrypted string crypt_str using pass_str as the pass-
word. crypt_str should be a string returned from ENCODE().

DECODE(crypt_str,pass_str)
RETURNS: string


MD5
This function calculates an MD5 checksum for the string. The value is returned as
a 32-character alphanumeric string. This is the same as the md5() function used

<<

. 124
( 132 .)



>>