Topics:
DVR
nvrec
Mplayer
Links
Misc
Commands
Humor
New user
uploaded files
|
|
(links)-> (Parent)->sql links and notes |
submited by Russell Thu 21 Apr 05
Edited Sat 05 Oct 13 |
mysql date and time functions
DATE_FORMAT(date,format)
Formats the date value according to the format string.
The following specifiers may be used in the format string:
Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week;
used with %X
%v Week (01..53), where Monday is the first day of the week;
used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week,
numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week,
numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric, two digits
%% A literal '%
For unix/http date formats such as :Sun, 20 March 2005 07:59:01
(ts is type timestamp,date, or datetime)
select DATE_FORMAT(ts,'%a, %d %M %Y %H:%i:%s') from tablename
for COOKIE date format. ( the date format used on the cookie experation date) use DATE_FORMAT(ts,'%a, %d-%b-%Y %H:%i:%s GMT'). To get a date in that format in the future (ie when you want to cookie to expire) use:
select date_format(DATE_ADD(now(), INTERVAL 24 HOUR),'%a, %d-%b-%Y %H:%i:%s GMT');
Will provide a cookie format date that will expire in 24 hours. (if your system clock isn't on GMT, adjust the number of hours accordingly) . The header ends up looking like this:
Set-Cookie: cookie-name=cookie-value; expires=Tue, 11-Oct-2011 15:40:43 GMT;
for time stamp format (YYYYMMDDHHMMSS) which is a text sortable time (even if the field is not a timestamp):
date_format(ts,'%Y%m%d%H%i%S')
select records with timestamp modified in the last 24 hours (ts is a timestamp):
select fields from table where ts> DATE_SUB(now(), INTERVAL 1 DAY)
This works for all date formats. Be sure to to the math on the "now()" value and not on ts, because I don't expect that sql will know not to do the date math on every value of ts if you use it like this "where date_add(ts,INTERVAL 1 DAY)>now()".
the DATE_SUB and DATE_ADD functions (search the page for "INTERVAL") support several time intervals includeing:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
This is only a partial list. To get records from the last hour, use
"where ts> DATE_SUB(now(), INTERVAL 1 HOUR)" (keep in mind, ts must be a field like TIMESTAMP or DATETIME that stores the hour. I leave to you, to guess why I thought I should mention this here ;-) )
Select records where the date is today
Select records where the date is today
SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())
Replys:
How to setup a MySQL cluster on Linux (Russell)
SQL how to select records according to the Hour of Date (Russell)
sql news and links (Russell)
find out version of mysql in linux machine via command line or query (Russell)
|
Add comment or question...:
|