PHP MySQL date & time reference

thank you for this great handy reference!

pasted verbatim:

Format PHP MySQL
Time The function is:
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
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];
day number of the year z
(Note: 0-365; ex: January 1st is 0)
(Note: 001-366; ex: January 1st is 001)
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.

Comments are closed.