Kangry.com: sql links and notes

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

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: