| Format | PHP | MySQL |
| Time | The function is: date(format) where "format" consists of the letters given below. | The functions are: date_format(string, format) time_format(string, format) where "format" consists of the letters given below.) |
| hour, 2-digit, 12-hour (01-12) | h | %h |
| hour, 2-digit, 24-hour (00-23) | H | %H |
| hour, numeric, 12-hour (1-12) | g | %l |
| hour, numeric, 24-hour (0-23) | G | %k |
| minute, 2-digit (00-59) | i | %i |
| seconds, 2-digit (00-59) | s | %S |
| time, 24-hour (hh:mm:ss) | none, use date("H:i:s") | %T |
| time, 12-hour (hh:mm:ss AM/PM) | none, use date("g:i:s A") | %r |
| AM / PM (uppercase letters) | A | %p |
| am / pm (lowercase letters) | a | none |
| Days | ||
| day name, full (Sunday) | l (a lowercase L) | %W |
| day name, abbreviation (Sun) | D | %a |
| day as number of week (0-6) | none, use a getdate() array: First do this: $dateInfo = getdate(); Your day as number of the week is $dateInfo[wday]; | %w |
| day number of the year | z (Note: 0-365; ex: January 1st is 0) | %j (Note: 001-366; ex: January 1st is 001) |
| Dates | ||
| month name in full (January) | F | %M |
| month name, abbreviated (Jan) | M | %b |
| month, 2-digit (01-12) | m | %m |
| month, no leading 0 (1, 2, 3 - 12) | n | %c |
| day of month, 2-digit (01, 02, ...) | d | %d |
| day of month, no leading 0 (1, 2) | j | %e |
| day of month with ordinal suffix (1st, 2nd, 3rd, 4th...) | none. Use jS | %D |
| ordinal suffix (st, nd, rd, th) | S | none; use %D (above) |
| year, 4 digit (2001) | Y | %Y |
| year, 2 digit (00-99) | y | %y |
| ||
| Examples: To write: | Use this: | Use this: |
| Sunday, June 03, 2001 | date('l, F d, Y') | date_format(date, '%W, %M %d, %Y') |
| Sun., Jun 3rd, 2001. 11:30 AM | date('D., M jS, Y.g:i A') | date_format(datetime, '%a., %b %D, %Y. %l: %i %p') Or if there are two columns, one date and one time, you'd use: date_format(date, '%a., %b %D, %Y.') time_format(time, '%l: %i %p') See the note below) |
| 2001-06-03 | date('Y-m-d') | (see important note below; this is default for date-type columns so you don't have to use any special function or formatting at all.) |
Important Note:
The choice in MySQL between date_format() and time_format() depends on the type of column you have stored your date in.
MySQL column type: | Data format in the column (all parts are required) | Example: | Use: |
date | YYYY-MM-DD | 2001-06-03 | date_format() to get date values |
time (see "NB" below) | hh:mm:ss | 13:30:00 | time_format() to get time values |
datetime | YYY-MM-DD hh:mm:ss | 2001-06-03 13:30:00 | date_format() to get date and/or time values |
NB: the time type for MySQL columns actually represents an amount of time elapsed; thus, it can be negative or positive. However, it can also be used to represent the time of day, where it represents the amount of time elapsed since 1 second past midnight. If you forget to add :00 for seconds (for example, using 13:30 for 1:30 p.m.), your time value will be interpreted as the number of minutes and seconds after midnight, or 12:30 a.m. plus 30 seconds.