funciones de fecha y hora

6/12/2014 MySQL::MySQL5.0ReferenceManual::12.7DateandTimeFunctions

http://dev.mysql.com/doc/refman/5.0/en/dateandtimefunctions.html 1/42

12.6.2MathematicalFunctions12.8WhatCalendarIsUsedByMySQL?

SectionNavigation[Toggle]

12FunctionsandOperators12.1FunctionandOperatorReference12.2TypeConversioninExpressionEvaluation12.3Operators12.4ControlFlowFunctions12.5StringFunctions12.6NumericFunctionsandOperators12.7DateandTimeFunctions12.8WhatCalendarIsUsedByMySQL?12.9FullTextSearchFunctions12.10CastFunctionsandOperators12.11BitFunctions12.12EncryptionandCompressionFunctions12.13InformationFunctions12.14SpatialAnalysisFunctions12.15MiscellaneousFunctions12.16FunctionsandModifiersforUsewithGROUPBYClauses12.17PrecisionMath

MySQL5.0ReferenceManual::12FunctionsandOperators::12.7DateandTimeFunctions

12.7DateandTimeFunctionsThissectiondescribesthefunctionsthatcanbeusedtomanipulatetemporalvalues.SeeSection11.3,DateandTimeTypes,foradescriptionoftherangeofvalueseachdateandtimetypehasandthevalidformatsinwhichvaluesmaybespecified.

Table12.13Date/TimeFunctions

Name DescriptionADDDATE() Addtimevalues(intervals)toadatevalueADDTIME() AddtimeCONVERT_TZ() ConvertfromonetimezonetoanotherCURDATE() ReturnthecurrentdateCURRENT_DATE(),CURRENT_DATE

SynonymsforCURDATE()

CURRENT_TIME(),CURRENT_TIME

SynonymsforCURTIME()

CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP

SynonymsforNOW()

CURTIME() ReturnthecurrenttimeDATE_ADD() Addtimevalues(intervals)toadatevalueDATE_FORMAT() FormatdateasspecifiedDATE_SUB() Subtractatimevalue(interval)fromadateDATE() ExtractthedatepartofadateordatetimeexpressionDATEDIFF() SubtracttwodatesDAY() SynonymforDAYOFMONTH()DAYNAME() ReturnthenameoftheweekdayDAYOFMONTH() Returnthedayofthemonth(031)DAYOFWEEK() ReturntheweekdayindexoftheargumentDAYOFYEAR() Returnthedayoftheyear(1366)EXTRACT() ExtractpartofadateFROM_DAYS() ConvertadaynumbertoadateFROM_UNIXTIME() FormatUNIXtimestampasadateGET_FORMAT() ReturnadateformatstringHOUR() ExtractthehourLAST_DAY ReturnthelastdayofthemonthfortheargumentLOCALTIME(),LOCALTIME SynonymforNOW()LOCALTIMESTAMP,LOCALTIMESTAMP()

SynonymforNOW()

MAKEDATE() CreateadatefromtheyearanddayofyearMAKETIME() Createtimefromhour,minute,secondMICROSECOND() ReturnthemicrosecondsfromargumentMINUTE() ReturntheminutefromtheargumentMONTH() ReturnthemonthfromthedatepassedMONTHNAME() ReturnthenameofthemonthNOW() ReturnthecurrentdateandtimePERIOD_ADD() AddaperiodtoayearmonthPERIOD_DIFF() ReturnthenumberofmonthsbetweenperiodsQUARTER() ReturnthequarterfromadateargumentSEC_TO_TIME() Convertssecondsto'HH:MM:SS'formatSECOND() Returnthesecond(059)STR_TO_DATE() ConvertastringtoadateSUBDATE() SynonymforDATE_SUB()wheninvokedwiththreeargumentsSUBTIME() SubtracttimesSYSDATE() ReturnthetimeatwhichthefunctionexecutesTIME_FORMAT() FormatastimeTIME_TO_SEC() ReturntheargumentconvertedtosecondsTIME() ExtractthetimeportionoftheexpressionpassedTIMEDIFF() SubtracttimeTIMESTAMP() Withasingleargument,thisfunctionreturnsthedateordatetimeexpression

withtwoarguments,thesumoftheargumentsTIMESTAMPADD() AddanintervaltoadatetimeexpressionTIMESTAMPDIFF() SubtractanintervalfromadatetimeexpressionTO_DAYS() ReturnthedateargumentconvertedtodaysUNIX_TIMESTAMP() ReturnaUNIXtimestampUTC_DATE() ReturnthecurrentUTCdateUTC_TIME() ReturnthecurrentUTCtimeUTC_TIMESTAMP() ReturnthecurrentUTCdateandtimeWEEK() ReturntheweeknumberWEEKDAY() ReturntheweekdayindexWEEKOFYEAR() Returnthecalendarweekofthedate(053)YEAR() ReturntheyearYEARWEEK() Returntheyearandweek

Hereisanexamplethatusesdatefunctions.Thefollowingqueryselectsallrowswithadate_colvaluefromwithinthelast30days:



mysql>SELECTsomethingFROMtbl_name

>WHEREDATE_SUB(CURDATE(),INTERVAL30DAY)



>'20080613'

mysql>SELECTCURDATE()+0

>20080613

CURRENT_DATE,CURRENT_DATE()

CURRENT_DATEandCURRENT_DATE()aresynonymsforCURDATE().

CURRENT_TIME,CURRENT_TIME()

CURRENT_TIMEandCURRENT_TIME()aresynonymsforCURTIME().

CURRENT_TIMESTAMP,CURRENT_TIMESTAMP()

CURRENT_TIMESTAMPandCURRENT_TIMESTAMP()aresynonymsforNOW().

CURTIME()

Returnsthecurrenttimeasavaluein'HH:MM:SS'orHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.Thevalueisexpressedinthecurrenttimezone.

mysql>SELECTCURTIME()

>'23:50:26'

mysql>SELECTCURTIME()+0

>235026.000000

DATE(expr)

Extractsthedatepartofthedateordatetimeexpressionexpr.

mysql>SELECTDATE('2003123101:02:03')

>'20031231'

DATEDIFF(expr1,expr2)

DATEDIFF()returnsexpr1expr2expressedasavalueindaysfromonedatetotheother.expr1andexpr2aredateordateandtimeexpressions.Onlythedatepartsofthevaluesareusedinthecalculation.

mysql>SELECTDATEDIFF('2007123123:59:59','20071230')

>1

mysql>SELECTDATEDIFF('2010113023:59:59','20101231')

>31

DATE_ADD(date,INTERVALexprunit),DATE_SUB(date,INTERVALexprunit)

Thesefunctionsperformdatearithmetic.Thedateargumentspecifiesthestartingdateordatetimevalue.exprisanexpressionspecifyingtheintervalvaluetobeaddedorsubtractedfromthestartingdate.exprisastringitmaystartwithafornegativeintervals.unitisakeywordindicatingtheunitsinwhichtheexpressionshouldbeinterpreted.

TheINTERVALkeywordandtheunitspecifierarenotcasesensitive.

Thefollowingtableshowstheexpectedformoftheexprargumentforeachunitvalue.

unitValue ExpectedexprFormatMICROSECOND MICROSECONDS

SECOND SECONDS

MINUTE MINUTES

HOUR HOURS

DAY DAYS

WEEK WEEKS

MONTH MONTHS

QUARTER QUARTERS

YEAR YEARS

SECOND_MICROSECOND 'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'

MINUTE_SECOND 'MINUTES:SECONDS'

HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'

HOUR_SECOND 'HOURS:MINUTES:SECONDS'

HOUR_MINUTE 'HOURS:MINUTES'

DAY_MICROSECOND 'DAYSHOURS:MINUTES:SECONDS.MICROSECONDS'

DAY_SECOND 'DAYSHOURS:MINUTES:SECONDS'

DAY_MINUTE 'DAYSHOURS:MINUTES'

DAY_HOUR 'DAYSHOURS'

YEAR_MONTH 'YEARSMONTHS'

Thereturnvaluedependsonthearguments:

DATETIMEifthefirstargumentisaDATETIME(orTIMESTAMP)value,orifthefirstargumentisaDATEandtheunitvalueusesHOURS,MINUTES,orSECONDS.

Stringotherwise.

ToensurethattheresultisDATETIME,youcanuseCAST()toconvertthefirstargumenttoDATETIME.



MySQLpermitsanypunctuationdelimiterintheexprformat.Thoseshowninthetablearethesuggesteddelimiters.IfthedateargumentisaDATEvalueandyourcalculationsinvolveonlyYEAR,MONTH,andDAYparts(thatis,notimeparts),theresultisaDATEvalue.Otherwise,theresultisaDATETIMEvalue.

DatearithmeticalsocanbeperformedusingINTERVALtogetherwiththe+oroperator:

date+INTERVALexprunit

dateINTERVALexprunit

INTERVALexprunit ispermittedoneithersideofthe+operatoriftheexpressionontheothersideisadateordatetimevalue.Fortheoperator,INTERVALexprunit ispermittedonlyontherightside,becauseitmakesnosensetosubtractadateordatetimevaluefromaninterval.

mysql>SELECT'2008123123:59:59'+INTERVAL1SECOND

>'2009010100:00:00'

mysql>SELECTINTERVAL1DAY+'20081231'

>'20090101'

mysql>SELECT'20050101'INTERVAL1SECOND

>'2004123123:59:59'

mysql>SELECTDATE_ADD('2000123123:59:59',

>INTERVAL1SECOND)

>'2001010100:00:00'


>INTERVAL1DAY)

>'2011010123:59:59'


>INTERVAL'1:1'MINUTE_SECOND)

>'2101010100:01:00'

mysql>SELECTDATE_SUB('2005010100:00:00',

>INTERVAL'11:1:1'DAY_SECOND)

>'2004123022:58:59'


>INTERVAL'110'DAY_HOUR)

>'1899123014:00:00'

mysql>SELECTDATE_SUB('19980102',INTERVAL31DAY)

>'19971202'

mysql>SELECTDATE_ADD('1992123123:59:59.000002',

>INTERVAL'1.999999'SECOND_MICROSECOND)

>'1993010100:00:01.000001'

Ifyouspecifyanintervalvaluethatistooshort(doesnotincludealltheintervalpartsthatwouldbeexpectedfromtheunitkeyword),MySQLassumesthatyouhaveleftouttheleftmostpartsoftheintervalvalue.Forexample,ifyouspecifyaunitofDAY_SECOND,thevalueofexprisexpectedtohavedays,hours,minutes,andsecondsparts.Ifyouspecifyavaluelike'1:10',MySQLassumesthatthedaysandhourspartsaremissingandthevaluerepresentsminutesandseconds.Inotherwords,'1:10'DAY_SECONDisinterpretedinsuchawaythatitisequivalentto'1:10'MINUTE_SECOND.ThisisanalogoustothewaythatMySQLinterpretsTIMEvaluesasrepresentingelapsedtimeratherthanasatimeofday.

Becauseexpristreatedasastring,becarefulifyouspecifyanonstringvaluewithINTERVAL.Forexample,withanintervalspecifierofHOUR_MINUTE,6/4evaluatesto1.5000andistreatedas1hour,5000minutes:

mysql>SELECT6/4

>1.5000

mysql>SELECTDATE_ADD('20090101',INTERVAL6/4HOUR_MINUTE)

>'2009010412:20:00'

Toensureinterpretationoftheintervalvalueasyouexpect,aCAST()operationmaybeused.Totreat6/4as1hour,5minutes,castittoaDECIMALvaluewithasinglefractionaldigit:

mysql>SELECTCAST(6/4ASDECIMAL(3,1))

>1.5


>INTERVALCAST(6/4ASDECIMAL(3,1))HOUR_MINUTE)

>'1970010113:05:00'

Ifyouaddtoorsubtractfromadatevaluesomethingthatcontainsatimepart,theresultisautomaticallyconvertedtoadatetimevalue:

mysql>SELECTDATE_ADD('20130101',INTERVAL1DAY)

>'20130102'

mysql>SELECTDATE_ADD('20130101',INTERVAL1HOUR)

>'2013010101:00:00'

IfyouaddMONTH,YEAR_MONTH,orYEARandtheresultingdatehasadaythatislargerthanthemaximumdayforthenewmonth,thedayisadjustedtothemaximumdaysinthenewmonth:

mysql>SELECTDATE_ADD('20090130',INTERVAL1MONTH)

>'20090228'

Datearithmeticoperationsrequirecompletedatesanddonotworkwithincompletedatessuchas'20060700'orbadlymalformeddates:

mysql>SELECTDATE_ADD('20060700',INTERVAL1DAY)



>NULL

mysql>SELECT'20050332'+INTERVAL1MONTH

>NULL

DATE_FORMAT(date,format)

Formatsthedatevalueaccordingtotheformatstring.

Thefollowingspecifiersmaybeusedintheformatstring.The%characterisrequiredbeforeformatspecifiercharacters.

Specifier Description%a Abbreviatedweekdayname(Sun..Sat)%b Abbreviatedmonthname(Jan..Dec)%c Month,numeric(0..12)%D DayofthemonthwithEnglishsuffix(0th,1st,2nd,3rd,)%d Dayofthemonth,numeric(00..31)%e Dayofthemonth,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 Dayofyear(001..366)%k Hour(0..23)%l Hour(1..12)%M Monthname(January..December)%m Month,numeric(00..12)%p AMorPM%r Time,12hour(hh:mm:ssfollowedbyAMorPM)%S Seconds(00..59)%s Seconds(00..59)%T Time,24hour(hh:mm:ss)%U Week(00..53),whereSundayisthefirstdayoftheweekWEEK()mode0%u Week(00..53),whereMondayisthefirstdayoftheweekWEEK()mode1%V Week(01..53),whereSundayisthefirstdayoftheweekWEEK()mode2usedwith%X%v Week(01..53),whereMondayisthefirstdayoftheweekWEEK()mode3usedwith%x%W Weekdayname(Sunday..Saturday)%w Dayoftheweek(0=Sunday..6=Saturday)%X YearfortheweekwhereSundayisthefirstdayoftheweek,numeric,fourdigitsusedwith%V%x Yearfortheweek,whereMondayisthefirstdayoftheweek,numeric,fourdigitsusedwith%v%Y Year,numeric,fourdigits%y Year,numeric(twodigits)%% Aliteral%character%x x ,foranyx notlistedabove

RangesforthemonthanddayspecifiersbeginwithzeroduetothefactthatMySQLpermitsthestoringofincompletedatessuchas'20140000'.

AsofMySQL5.0.25,thelanguageusedfordayandmonthnamesandabbreviationsiscontrolledbythevalueofthelc_time_namessystemvariable(Section10.7,MySQLServerLocaleSupport).

Forthe%U,%u,%V,and%vspecifiers,seethedescriptionoftheWEEK()functionforinformationaboutthemodevalues.Themodeaffectshowweeknumberingoccurs.

AsofMySQL5.0.36,DATE_FORMAT()returnsastringwithacharactersetandcollationgivenbycharacter_set_connectionandcollation_connectionsothatitcanreturnmonthandweekdaynamescontainingnonASCIIcharacters.Before5.0.36,thereturnvalueisabinarystring.

mysql>SELECTDATE_FORMAT('2009100422:23:00','%W%M%Y')

>'SundayOctober2009'

mysql>SELECTDATE_FORMAT('2007100422:23:00','%H:%i:%s')

>'22:23:00'

mysql>SELECTDATE_FORMAT('1900100422:23:00',

>'%D%y%a%d%m%b%j')

>'4th00Thu0410Oct277'

mysql>SELECTDATE_FORMAT('1997100422:23:00',

>'%H%k%I%r%T%S%w')

>'22221010:23:00PM22:23:00006'

mysql>SELECTDATE_FORMAT('19990101','%X%V')

>'199852'

mysql>SELECTDATE_FORMAT('20060600','%d')

>'00'

DATE_SUB(date,INTERVALexprunit)

SeethedescriptionforDATE_ADD().

DAY(date)

DAY()isasynonymforDAYOFMONTH().



DAYNAME(date)

Returnsthenameoftheweekdayfordate.AsofMySQL5.0.25,thelanguageusedforthenameiscontrolledbythevalueofthelc_time_namessystemvariable(Section10.7,MySQLServerLocaleSupport).

mysql>SELECTDAYNAME('20070203')

>'Saturday'

DAYOFMONTH(date)

Returnsthedayofthemonthfordate,intherange1to31,or0fordatessuchas'00000000'or'20080000'thathaveazerodaypart.

mysql>SELECTDAYOFMONTH('20070203')

>3

DAYOFWEEK(date)

Returnstheweekdayindexfordate(1=Sunday,2=Monday,,7=Saturday).TheseindexvaluescorrespondtotheODBCstandard.

mysql>SELECTDAYOFWEEK('20070203')

>7

DAYOFYEAR(date)

Returnsthedayoftheyearfordate,intherange1to366.

mysql>SELECTDAYOFYEAR('20070203')

>34

EXTRACT(unitFROMdate)

TheEXTRACT()functionusesthesamekindsofunitspecifiersasDATE_ADD()orDATE_SUB(),butextractspartsfromthedateratherthanperformingdatearithmetic.

mysql>SELECTEXTRACT(YEARFROM'20090702')

>2009

mysql>SELECTEXTRACT(YEAR_MONTHFROM'2009070201:02:03')

>200907

mysql>SELECTEXTRACT(DAY_MINUTEFROM'2009070201:02:03')

>20102

mysql>SELECTEXTRACT(MICROSECOND

>FROM'2003010210:30:00.000123')

>123

FROM_DAYS(N)

GivenadaynumberN,returnsaDATEvalue.

mysql>SELECTFROM_DAYS(730669)

>'20070703'

UseFROM_DAYS()withcautiononolddates.ItisnotintendedforusewithvaluesthatprecedetheadventoftheGregoriancalendar(1582).SeeSection12.8,WhatCalendarIsUsedByMySQL?.

FROM_UNIXTIME(unix_timestamp),FROM_UNIXTIME(unix_timestamp,format)

Returnsarepresentationoftheunix_timestampargumentasavaluein'YYYYMMDDHH:MM:SS'orYYYYMMDDHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.Thevalueisexpressedinthecurrenttimezone.unix_timestampisaninternaltimestampvaluesuchasisproducedbytheUNIX_TIMESTAMP()function.

Ifformatisgiven,theresultisformattedaccordingtotheformatstring,whichisusedthesamewayaslistedintheentryfortheDATE_FORMAT()function.

mysql>SELECTFROM_UNIXTIME(1196440219)

>'2007113010:30:19'

mysql>SELECTFROM_UNIXTIME(1196440219)+0

>20071130103019.000000

mysql>SELECTFROM_UNIXTIME(UNIX_TIMESTAMP(),

>'%Y%D%M%h:%i:%s%x')

>'200730thNovember10:30:592007'

Note:IfyouuseUNIX_TIMESTAMP()andFROM_UNIXTIME()toconvertbetweenTIMESTAMPvaluesandUnixtimestampvalues,theconversionislossybecausethemappingisnotonetooneinbothdirections.Fordetails,seethedescriptionoftheUNIX_TIMESTAMP()function.

GET_FORMAT({DATE|TIME|DATETIME},{'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

Returnsaformatstring.ThisfunctionisusefulincombinationwiththeDATE_FORMAT()andtheSTR_TO_DATE()functions.

Thepossiblevaluesforthefirstandsecondargumentsresultinseveralpossibleformatstrings(forthespecifiersused,seethetableintheDATE_FORMAT()functiondescription).ISOformatreferstoISO9075,notISO8601.

FunctionCall Result



GET_FORMAT(DATE,'USA') '%m.%d.%Y'

GET_FORMAT(DATE,'JIS') '%Y%m%d'

GET_FORMAT(DATE,'ISO') '%Y%m%d'

GET_FORMAT(DATE,'EUR') '%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL') '%Y%m%d'

GET_FORMAT(DATETIME,'USA') '%Y%m%d%H.%i.%s'

GET_FORMAT(DATETIME,'JIS') '%Y%m%d%H:%i:%s'

GET_FORMAT(DATETIME,'ISO') '%Y%m%d%H:%i:%s'

GET_FORMAT(DATETIME,'EUR') '%Y%m%d%H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL') '%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA') '%h:%i:%s%p'

GET_FORMAT(TIME,'JIS') '%H:%i:%s'

GET_FORMAT(TIME,'ISO') '%H:%i:%s'

GET_FORMAT(TIME,'EUR') '%H.%i.%s'

GET_FORMAT(TIME,'INTERNAL') '%H%i%s'

TIMESTAMPcanalsobeusedasthefirstargumenttoGET_FORMAT(),inwhichcasethefunctionreturnsthesamevaluesasforDATETIME.

mysql>SELECTDATE_FORMAT('20031003',GET_FORMAT(DATE,'EUR'))

>'03.10.2003'

mysql>SELECTSTR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'))

>'20031031'

HOUR(time)

Returnsthehourfortime.Therangeofthereturnvalueis0to23fortimeofdayvalues.However,therangeofTIMEvaluesactuallyismuchlarger,soHOURcanreturnvaluesgreaterthan23.

mysql>SELECTHOUR('10:05:03')

>10

mysql>SELECTHOUR('272:59:59')

>272

LAST_DAY(date)

Takesadateordatetimevalueandreturnsthecorrespondingvalueforthelastdayofthemonth.ReturnsNULLiftheargumentisinvalid.

mysql>SELECTLAST_DAY('20030205')

>'20030228'


>'20040229'

mysql>SELECTLAST_DAY('2004010101:01:01')

>'20040131'


>NULL

LOCALTIME,LOCALTIME()

LOCALTIMEandLOCALTIME()aresynonymsforNOW().

LOCALTIMESTAMP,LOCALTIMESTAMP()

LOCALTIMESTAMPandLOCALTIMESTAMP()aresynonymsforNOW().

MAKEDATE(year,dayofyear)

Returnsadate,givenyearanddayofyearvalues.dayofyearmustbegreaterthan0ortheresultisNULL.

mysql>SELECTMAKEDATE(2011,31),MAKEDATE(2011,32)

>'20110131','20110201'

mysql>SELECTMAKEDATE(2011,365),MAKEDATE(2014,365)

>'20111231','20141231'

mysql>SELECTMAKEDATE(2011,0)

>NULL

MAKETIME(hour,minute,second)

Returnsatimevaluecalculatedfromthehour,minute,andsecondarguments.

mysql>SELECTMAKETIME(12,15,30)

>'12:15:30'

MICROSECOND(expr)

Returnsthemicrosecondsfromthetimeordatetimeexpressionexprasanumberintherangefrom0to999999.

mysql>SELECTMICROSECOND('12:00:00.123456')

>123456

mysql>SELECTMICROSECOND('2009123123:59:59.000010')

>10



MINUTE(time)

Returnstheminutefortime,intherange0to59.

mysql>SELECTMINUTE('2008020310:05:03')

>5

MONTH(date)

Returnsthemonthfordate,intherange1to12forJanuarytoDecember,or0fordatessuchas'00000000'or'20080000'thathaveazeromonthpart.

mysql>SELECTMONTH('20080203')

>2

MONTHNAME(date)

Returnsthefullnameofthemonthfordate.AsofMySQL5.0.25,thelanguageusedforthenameiscontrolledbythevalueofthelc_time_namessystemvariable(Section10.7,MySQLServerLocaleSupport).

mysql>SELECTMONTHNAME('20080203')

>'February'

NOW()

Returnsthecurrentdateandtimeasavaluein'YYYYMMDDHH:MM:SS'orYYYYMMDDHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.Thevalueisexpressedinthecurrenttimezone.

mysql>SELECTNOW()

>'2007121523:50:26'

mysql>SELECTNOW()+0

>20071215235026.000000

NOW()returnsaconstanttimethatindicatesthetimeatwhichthestatementbegantoexecute.(Withinastoredfunctionortrigger,NOW()returnsthetimeatwhichthefunctionortriggeringstatementbegantoexecute.)ThisdiffersfromthebehaviorforSYSDATE(),whichreturnstheexacttimeatwhichitexecutesasofMySQL5.0.12.

mysql>SELECTNOW(),SLEEP(2),NOW()

++++

|NOW()|SLEEP(2)|NOW()|

++++

|2006041213:47:36|0|2006041213:47:36|

++++

mysql>SELECTSYSDATE(),SLEEP(2),SYSDATE()

++++

|SYSDATE()|SLEEP(2)|SYSDATE()|

++++

|2006041213:47:44|0|2006041213:47:46|

++++

Inaddition,theSETTIMESTAMPstatementaffectsthevaluereturnedbyNOW()butnotbySYSDATE().ThismeansthattimestampsettingsinthebinaryloghavenoeffectoninvocationsofSYSDATE().SettingthetimestamptoanonzerovaluecauseseachsubsequentinvocationofNOW()toreturnthatvalue.SettingthetimestamptozerocancelsthiseffectsothatNOW()onceagainreturnsthecurrentdateandtime.

SeethedescriptionforSYSDATE()foradditionalinformationaboutthedifferencesbetweenthetwofunctions.

PERIOD_ADD(P,N)

AddsNmonthstoperiodP(intheformatYYMMorYYYYMM).ReturnsavalueintheformatYYYYMM.NotethattheperiodargumentPisnotadatevalue.

mysql>SELECTPERIOD_ADD(200801,2)

>200803

PERIOD_DIFF(P1,P2)

ReturnsthenumberofmonthsbetweenperiodsP1andP2.P1andP2shouldbeintheformatYYMMorYYYYMM.NotethattheperiodargumentsP1andP2arenotdatevalues.

mysql>SELECTPERIOD_DIFF(200802,200703)

>11

QUARTER(date)

Returnsthequarteroftheyearfordate,intherange1to4.

mysql>SELECTQUARTER('20080401')

>2

SECOND(time)

Returnsthesecondfortime,intherange0to59.

mysql>SELECTSECOND('10:05:03')



>3

SEC_TO_TIME(seconds)

Returnsthesecondsargument,convertedtohours,minutes,andseconds,asaTIMEvalue.TherangeoftheresultisconstrainedtothatoftheTIMEdatatype.Awarningoccursiftheargumentcorrespondstoavalueoutsidethatrange.

mysql>SELECTSEC_TO_TIME(2378)

>'00:39:38'

mysql>SELECTSEC_TO_TIME(2378)+0

>3938

STR_TO_DATE(str,format)

ThisistheinverseoftheDATE_FORMAT()function.Ittakesastringstrandaformatstringformat.STR_TO_DATE()returnsaDATETIMEvalueiftheformatstringcontainsbothdateandtimeparts,oraDATEorTIMEvalueifthestringcontainsonlydateortimeparts.Ifthedate,time,ordatetimevalueextractedfromstrisillegal,STR_TO_DATE()returnsNULLand,asofMySQL5.0.3,producesawarning.

Theserverscansstrattemptingtomatchformattoit.Theformatstringcancontainliteralcharactersandformatspecifiersbeginningwith%.Literalcharactersinformatmustmatchliterallyinstr.Formatspecifiersinformatmustmatchadateortimepartinstr.Forthespecifiersthatcanbeusedinformat,seetheDATE_FORMAT()functiondescription.

mysql>SELECTSTR_TO_DATE('01,5,2013','%d,%m,%Y')

>'20130501'

mysql>SELECTSTR_TO_DATE('May1,2013','%M%d,%Y')

>'20130501'

Scanningstartsatthebeginningofstrandfailsifformatisfoundnottomatch.Extracharactersattheendofstrareignored.

mysql>SELECTSTR_TO_DATE('a09:30:17','a%h:%i:%s')

>'09:30:17'

mysql>SELECTSTR_TO_DATE('a09:30:17','%h:%i:%s')

>NULL

mysql>SELECTSTR_TO_DATE('09:30:17a','%h:%i:%s')

>'09:30:17'

Unspecifieddateortimepartshaveavalueof0,soincompletelyspecifiedvaluesinstrproducearesultwithsomeorallpartssetto0:

mysql>SELECTSTR_TO_DATE('abc','abc')

>'00000000'

mysql>SELECTSTR_TO_DATE('9','%m')

>'00000900'

mysql>SELECTSTR_TO_DATE('9','%s')

>'00:00:09'

RangecheckingonthepartsofdatevaluesisasdescribedinSection11.3.1,TheDATE,DATETIME,andTIMESTAMPTypes.Thismeans,forexample,thatzerodatesordateswithpartvaluesof0arepermittedunlesstheSQLmodeissettodisallowsuchvalues.

mysql>SELECTSTR_TO_DATE('00/00/0000','%m/%d/%Y')

>'00000000'

mysql>SELECTSTR_TO_DATE('04/31/2004','%m/%d/%Y')

>'20040431'

Note

Youcannotuseformat"%X%V"toconvertayearweekstringtoadatebecausethecombinationofayearandweekdoesnotuniquelyidentifyayearandmonthiftheweekcrossesamonthboundary.Toconvertayearweektoadate,youshouldalsospecifytheweekday:

mysql>SELECTSTR_TO_DATE('200442Monday','%X%V%W')

>'20041018'

SUBDATE(date,INTERVALexprunit),SUBDATE(expr,days)

WheninvokedwiththeINTERVALformofthesecondargument,SUBDATE()isasynonymforDATE_SUB().ForinformationontheINTERVALunitargument,seethediscussionforDATE_ADD().

mysql>SELECTDATE_SUB('20080102',INTERVAL31DAY)

>'20071202'

mysql>SELECTSUBDATE('20080102',INTERVAL31DAY)

>'20071202'

Thesecondformenablestheuseofanintegervaluefordays.Insuchcases,itisinterpretedasthenumberofdaystobesubtractedfromthedateordatetimeexpressionexpr.

mysql>SELECTSUBDATE('2008010212:00:00',31)

>'2007120212:00:00'

SUBTIME(expr1,expr2)



SUBTIME()returnsexpr1expr2expressedasavalueinthesameformatasexpr1.expr1isatimeordatetimeexpression,andexpr2isatimeexpression.

mysql>SELECTSUBTIME('2007123123:59:59.999999','11:1:1.000002')

>'2007123022:58:58.999997'

mysql>SELECTSUBTIME('01:00:00.999999','02:00:00.999998')

>'00:59:59.999999'

SYSDATE()

Returnsthecurrentdateandtimeasavaluein'YYYYMMDDHH:MM:SS'orYYYYMMDDHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.

AsofMySQL5.0.12,SYSDATE()returnsthetimeatwhichitexecutes.ThisdiffersfromthebehaviorforNOW(),whichreturnsaconstanttimethatindicatesthetimeatwhichthestatementbegantoexecute.(Withinastoredfunctionortrigger,NOW()returnsthetimeatwhichthefunctionortriggeringstatementbegantoexecute.)

mysql>SELECTNOW(),SLEEP(2),NOW()

++++

|NOW()|SLEEP(2)|NOW()|

++++

|2006041213:47:36|0|2006041213:47:36|

++++

mysql>SELECTSYSDATE(),SLEEP(2),SYSDATE()

++++

|SYSDATE()|SLEEP(2)|SYSDATE()|

++++

|2006041213:47:44|0|2006041213:47:46|

++++

Inaddition,theSETTIMESTAMPstatementaffectsthevaluereturnedbyNOW()butnotbySYSDATE().ThismeansthattimestampsettingsinthebinaryloghavenoeffectoninvocationsofSYSDATE().

BecauseSYSDATE()canreturndifferentvaluesevenwithinthesamestatement,andisnotaffectedbySETTIMESTAMP,itisnondeterministicandthereforeunsafeforreplication.Ifthatisaproblem,youcanstarttheserverwiththesysdateisnowoptiontocauseSYSDATE()tobeanaliasforNOW().ThenondeterministicnatureofSYSDATE()alsomeansthatindexescannotbeusedforevaluatingexpressionsthatrefertoit.

TIME(expr)

Extractsthetimepartofthetimeordatetimeexpressionexprandreturnsitasastring.

mysql>SELECTTIME('2003123101:02:03')

>'01:02:03'

mysql>SELECTTIME('2003123101:02:03.000123')

>'01:02:03.000123'

TIMEDIFF(expr1,expr2)

TIMEDIFF()returnsexpr1expr2expressedasatimevalue.expr1andexpr2aretimeordateandtimeexpressions,butbothmustbeofthesametype.

TheresultreturnedbyTIMEDIFF()islimitedtotherangeallowedforTIMEvalues.Alternatively,youcanuseeitherofthefunctionsTIMESTAMPDIFF()andUNIX_TIMESTAMP(),bothofwhichreturnintegers.

mysql>SELECTTIMEDIFF('2000:01:0100:00:00',

>'2000:01:0100:00:00.000001')

>'00:00:00.000001'

mysql>SELECTTIMEDIFF('2008123123:59:59.000001',

>'2008123001:01:01.000002')

>'46:58:57.999999'

TIMESTAMP(expr),TIMESTAMP(expr1,expr2)

Withasingleargument,thisfunctionreturnsthedateordatetimeexpressionexprasadatetimevalue.Withtwoarguments,itaddsthetimeexpressionexpr2tothedateordatetimeexpressionexpr1andreturnstheresultasadatetimevalue.

mysql>SELECTTIMESTAMP('20031231')

>'2003123100:00:00'

mysql>SELECTTIMESTAMP('2003123112:00:00','12:00:00')

>'2004010100:00:00'

TIMESTAMPADD(unit,interval,datetime_expr)

Addstheintegerexpressionintervaltothedateordatetimeexpressiondatetime_expr.Theunitforintervalisgivenbytheunitargument,whichshouldbeoneofthefollowingvalues:FRAC_SECOND(microseconds),SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,orYEAR.

BeginningwithMySQL5.0.60,itispossibletouseMICROSECONDinplaceofFRAC_SECONDwiththisfunction,andFRAC_SECONDisdeprecated.FRAC_SECONDisremovedinMySQL5.5.

Theunitvaluemaybespecifiedusingoneofkeywordsasshown,orwithaprefixofSQL_TSI_.Forexample,DAYandSQL_TSI_DAYbotharelegal.



mysql>SELECTTIMESTAMPADD(MINUTE,1,'20030102')

>'2003010200:01:00'

mysql>SELECTTIMESTAMPADD(WEEK,1,'20030102')

>'20030109'

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returnsdatetime_expr2datetime_expr1,wheredatetime_expr1anddatetime_expr2aredateordatetimeexpressions.Oneexpressionmaybeadateandtheotheradatetimeadatevalueistreatedasadatetimehavingthetimepart'00:00:00'wherenecessary.Theunitfortheresult(aninteger)isgivenbytheunitargument.ThelegalvaluesforunitarethesameasthoselistedinthedescriptionoftheTIMESTAMPADD()function.

mysql>SELECTTIMESTAMPDIFF(MONTH,'20030201','20030501')

>3

mysql>SELECTTIMESTAMPDIFF(YEAR,'20020501','20010101')

>1

mysql>SELECTTIMESTAMPDIFF(MINUTE,'20030201','2003050112:05:55')

>128885

Note

TheorderofthedateordatetimeargumentsforthisfunctionistheoppositeofthatusedwiththeTIMESTAMP()functionwheninvokedwith2arguments.

TIME_FORMAT(time,format)

ThisisusedliketheDATE_FORMAT()function,buttheformatstringmaycontainformatspecifiersonlyforhours,minutes,seconds,andmicroseconds.OtherspecifiersproduceaNULLvalueor0.

Ifthetimevaluecontainsanhourpartthatisgreaterthan23,the%Hand%khourformatspecifiersproduceavaluelargerthantheusualrangeof0..23.Theotherhourformatspecifiersproducethehourvaluemodulo12.

mysql>SELECTTIME_FORMAT('100:00:00','%H%k%h%I%l')

>'10010004044'

TIME_TO_SEC(time)

Returnsthetimeargument,convertedtoseconds.

mysql>SELECTTIME_TO_SEC('22:23:00')

>80580

mysql>SELECTTIME_TO_SEC('00:39:38')

>2378

TO_DAYS(date)

Givenadatedate,returnsadaynumber(thenumberofdayssinceyear0).

mysql>SELECTTO_DAYS(950501)

>728779

mysql>SELECTTO_DAYS('20071007')

>733321

TO_DAYS()isnotintendedforusewithvaluesthatprecedetheadventoftheGregoriancalendar(1582),becauseitdoesnottakeintoaccountthedaysthatwerelostwhenthecalendarwaschanged.Fordatesbefore1582(andpossiblyalateryearinotherlocales),resultsfromthisfunctionarenotreliable.SeeSection12.8,WhatCalendarIsUsedByMySQL?,fordetails.

RememberthatMySQLconvertstwodigityearvaluesindatestofourdigitformusingtherulesinSection11.3,DateandTimeTypes.Forexample,'20081007'and'081007'areseenasidenticaldates:

mysql>SELECTTO_DAYS('20081007'),TO_DAYS('081007')

>733687,733687

InMySQL,thezerodateisdefinedas'00000000',eventhoughthisdateisitselfconsideredinvalid.Thismeansthat,for'00000000'and'00000101',TO_DAYS()returnsthevaluesshownhere:


++

|to_days('00000000')|

++

|NULL|

++

1rowinset,1warning(0.00sec)

mysql>SHOWWARNINGS

++++

|Level|Code|Message|

++++

|Warning|1292|Incorrectdatetimevalue:'00000000'|

++++

1rowinset(0.00sec)




++

|to_days('00000101')|

++

|1|

++

1rowinset(0.00sec)

ThisistruewhetherornottheALLOW_INVALID_DATESSQLservermode(availableinMySQL5.0.2andlater)isenabled.

UNIX_TIMESTAMP(),UNIX_TIMESTAMP(date)

Ifcalledwithnoargument,returnsaUnixtimestamp(secondssince'1970010100:00:00'UTC)asanunsignedinteger.IfUNIX_TIMESTAMP()iscalledwithadateargument,itreturnsthevalueoftheargumentassecondssince'1970010100:00:00'UTC.datemaybeaDATEstring,aDATETIMEstring,aTIMESTAMP,oranumberintheformatYYMMDDorYYYYMMDD.TheserverinterpretsdateasavalueinthecurrenttimezoneandconvertsittoaninternalvalueinUTC.ClientscansettheirtimezoneasdescribedinSection10.6,MySQLServerTimeZoneSupport.

mysql>SELECTUNIX_TIMESTAMP()

>1196440210

mysql>SELECTUNIX_TIMESTAMP('2007113010:30:19')

>1196440219

WhenUNIX_TIMESTAMP()isusedonaTIMESTAMPcolumn,thefunctionreturnstheinternaltimestampvaluedirectly,withnoimplicitstringtoUnixtimestampconversion.IfyoupassanoutofrangedatetoUNIX_TIMESTAMP(),itreturns0.

Note:IfyouuseUNIX_TIMESTAMP()andFROM_UNIXTIME()toconvertbetweenTIMESTAMPvaluesandUnixtimestampvalues,theconversionislossybecausethemappingisnotonetooneinbothdirections.Forexample,duetoconventionsforlocaltimezonechanges,itispossiblefortwoUNIX_TIMESTAMP()tomaptwoTIMESTAMPvaluestothesameUnixtimestampvalue.FROM_UNIXTIME()willmapthatvaluebacktoonlyoneoftheoriginalTIMESTAMPvalues.Hereisanexample,usingTIMESTAMPvaluesintheCETtimezone:


++

|UNIX_TIMESTAMP('2005032703:00:00')|

++

|1111885200|

++


++

|UNIX_TIMESTAMP('2005032702:00:00')|

++

|1111885200|

++

mysql>SELECTFROM_UNIXTIME(1111885200)

++

|FROM_UNIXTIME(1111885200)|

++

|2005032703:00:00|

++

IfyouwanttosubtractUNIX_TIMESTAMP()columns,youmightwanttocasttheresulttosignedintegers.SeeSection12.10,CastFunctionsandOperators.

UTC_DATE,UTC_DATE()

ReturnsthecurrentUTCdateasavaluein'YYYYMMDD'orYYYYMMDDformat,dependingonwhetherthefunctionisusedinastringornumericcontext.

mysql>SELECTUTC_DATE(),UTC_DATE()+0

>'20030814',20030814

UTC_TIME,UTC_TIME()

ReturnsthecurrentUTCtimeasavaluein'HH:MM:SS'orHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.

mysql>SELECTUTC_TIME(),UTC_TIME()+0

>'18:07:53',180753.000000

UTC_TIMESTAMP,UTC_TIMESTAMP()

ReturnsthecurrentUTCdateandtimeasavaluein'YYYYMMDDHH:MM:SS'orYYYYMMDDHHMMSS.uuuuuuformat,dependingonwhetherthefunctionisusedinastringornumericcontext.

mysql>SELECTUTC_TIMESTAMP(),UTC_TIMESTAMP()+0

>'2003081418:08:04',20030814180804.000000

WEEK(date[,mode])

Thisfunctionreturnstheweeknumberfordate.ThetwoargumentformofWEEK()enablesyoutospecifywhethertheweekstartsonSundayorMondayandwhetherthereturnvalueshouldbeintherangefrom0to53



orfrom1to53.Ifthemodeargumentisomitted,thevalueofthedefault_week_formatsystemvariableisused.SeeSection5.1.4,ServerSystemVariables.

Thefollowingtabledescribeshowthemodeargumentworks.

Mode Firstdayofweek Range Week1isthefirstweek0 Sunday 053 withaSundayinthisyear1 Monday 053 with4ormoredaysthisyear2 Sunday 153 withaSundayinthisyear3 Monday 153 with4ormoredaysthisyear4 Sunday 053 with4ormoredaysthisyear5 Monday 053 withaMondayinthisyear6 Sunday 153 with4ormoredaysthisyear7 Monday 153 withaMondayinthisyear

Formodevalueswithameaningofwith4ormoredaysthisyear,weeksarenumberedaccordingtoISO8601:1988:

IftheweekcontainingJanuary1has4ormoredaysinthenewyear,itisweek1.

Otherwise,itisthelastweekofthepreviousyear,andthenextweekisweek1.

mysql>SELECTWEEK('20080220')

>7

mysql>SELECTWEEK('20080220',0)

>7


>8


>53

Notethatifadatefallsinthelastweekofthepreviousyear,MySQLreturns0ifyoudonotuse2,3,6,or7astheoptionalmodeargument:

mysql>SELECTYEAR('20000101'),WEEK('20000101',0)

>2000,0

OnemightarguethatWEEK()shouldreturn52becausethegivendateactuallyoccursinthe52ndweekof1999.WEEK()returns0insteadsothatthereturnvalueistheweeknumberinthegivenyear.ThismakesuseoftheWEEK()functionreliablewhencombinedwithotherfunctionsthatextractadatepartfromadate.

Ifyoupreferaresultevaluatedwithrespecttotheyearthatcontainsthefirstdayoftheweekforthegivendate,use0,2,5,or7astheoptionalmodeargument.


>52

Alternatively,usetheYEARWEEK()function:

mysql>SELECTYEARWEEK('20000101')

>199952

mysql>SELECTMID(YEARWEEK('20000101'),5,2)

>'52'

WEEKDAY(date)

Returnstheweekdayindexfordate(0=Monday,1=Tuesday,6=Sunday).

mysql>SELECTWEEKDAY('2008020322:23:00')

>6

mysql>SELECTWEEKDAY('20071106')

>1

WEEKOFYEAR(date)

Returnsthecalendarweekofthedateasanumberintherangefrom1to53.WEEKOFYEAR()isacompatibilityfunctionthatisequivalenttoWEEK(date,3).

mysql>SELECTWEEKOFYEAR('20080220')

>8

YEAR(date)

Returnstheyearfordate,intherange1000to9999,or0forthezerodate.

mysql>SELECTYEAR('19870101')

>1987

YEARWEEK(date),YEARWEEK(date,mode)

Returnsyearandweekforadate.ThemodeargumentworksexactlylikethemodeargumenttoWEEK().Theyearintheresultmaybedifferentfromtheyearinthedateargumentforthefirstandthelastweekoftheyear.

mysql>SELECTYEARWEEK('19870101')

>198653



NotethattheweeknumberisdifferentfromwhattheWEEK()functionwouldreturn(0)foroptionalarguments0or1,asWEEK()thenreturnstheweekinthecontextofthegivenyear.

Previous/Next/Up/TableofContents

UserCommentsPostedbyIsaacShepardonOctober1120032:53pm [Delete][Edit]

Ifyou'relookingforgenericSQLqueriesthatwillallowyoutogetthedays,months,andyearsbetweenanytwogivendates,youmightconsiderusingthese.Youjustneedtosubstitutedate1anddate2withyourdateexpressions.

NOTE:Someoftheseformulasarecomplexbecausetheyaccountforallcaseswheredate1date2.Additionally,theseformulascanbeusedinverygenericquerieswherealiasesandtemporaryvariablesarenotallowed.

Numberofdaysbetweendate1anddate2:

TO_DAYS(date2)TO_DAYS(date1)

Numberofmonthsbetweendate1anddate2:

IF((((YEAR(date2)1)*12+MONTH(date2))((YEAR(date1)1)*12+MONTH(date1)))>0,(((YEAR(date2)1)*12+MONTH(date2))((YEAR(date1)1)*12+MONTH(date1)))(MID(date2,9,2)



NotethatthebuiltindefaultvaluesfortheDATEandDATEFIELDcolumntypesisoutofrange.Forexample,00000000isavalidwayofexpressingNULL,butifthecolumnissetasNOTNULL,00000000isstillthedefaultvalue.ThiscancauseproblemswithsomeapplicationsusingMySQL.

PostedbyasdacfddsfdsfonJanuary2720043:25am [Delete][Edit]

Iwaslookingforafunctiontodetectifthecurrentweekisoddoreven.IcouldnotfindonesoIusethis:MOD((DATE_FORMAT(CURDATE(),"%v")),2)Theoutputisa'0'(even)ora'1'(odd)

PostedbySteveWestonFebruary15200410:49pm [Delete][Edit]

TocreateaDATETIMEofNOW()inUTCwithoutupgradingto4.1.1,justuse:

DATE_ADD('19700101',INTERVALUNIX_TIMESTAMP()SECOND)

Postedby[namewithheld]onMarch420049:39am [Delete][Edit]

workaroundforSTR_TO_DATEpreversion4.1.1.uglybutitseemstoworkfine.

assumption:youknowtheformatofthereceiveddate(inthebelowexampletheformatismm/dd/yy,m/d/yy,mm/dd/yyyy,etc)

thestatementextractstheyearbylocatingtheindexofthesecond'/'andreadingfromtherightofthestringtothatindex.theindexofthesecondis'/'isfoundbyusingLOCATEwiththeindexofthefirst'/'.itextractsthedaybylocatingtheindecesofthefirstandsecond'/'andreadingbetweenthemitextractsthemonthbylocatingtheindexofthefirst'/'andreadingfromtheleftofthestringtothatindex.itthenCONCATstheyearmonthanddaypiecestogetherseparatingthemwithhyphens.lastly,itletsDATE_FORMATdoitsmagiconthestring.

(replacetheteststring'1/11/03'withyourfieldname,etc)

selectDATE_FORMAT(CONCAT(RIGHT('1/11/03',length('1/11/03')LOCATE('/','1/11/03',LOCATE('/','1/11/03')+1)),'',LEFT('1/11/03',LOCATE('/','1/11/03')1),'',SUBSTRING('1/11/03',LOCATE('/','1/11/03')+1,LOCATE('/','1/11/03',LOCATE('/','1/11/03')+1)LOCATE('/','1/11/03')1)),'%Y%m%d')

PostedbyOlavAlexanderMjeldeonMarch15200411:15am [Delete][Edit]

Letssayyouhavethemysqlbefore4.1.1(wheretimediff()wasimplementet),andyouwanttodoatimediff.

Iwantedtomakea"activeusers"onmypage,butIfoundoutthatIdidnthavethetimedifffunction(tofindpersonswhichhavebeenactivewithin5minutes).

So,Ifiguredthisqueryout:

SELECTnickFROM`users`WHERETO_DAYS(NOW())TO_DAYS(last_login)



$lastLogin=strtotime($row['lastLogin'])$diff=$now$lastLogin$now=date('YmdHis',$now)

if($diff>3600){//3600secondsis1hour$query='UPDATEmembersSETlogins=logins+1,lastLogin='.$now.'WHEREmemberID='.$SEC_IDmysql_query($query)}

NowthedateenteredisthePHPtime(thataccountsforDST)andwearecomparingittoPHPtimesoalliswell.

IthinkthisapproachwillworkwellforanytimeyouwishtoenteradateintoMySQLusingPHP.Justformatthedateusingthe"YmdHis"formatstringandusethestrtotime()functiontoreadadateretrievedfromMySQL.

Theadvantagetothisapproachratherthanjustenteringthe"normal"PHPdateintoacharortextfieldisthatthedatesare"human"readableinthetableandalltheMySQLdate/timefunctionsareavailableforfuturequeries.

PostedbyMartinSchwedesonApril2520049:11am [Delete][Edit]

tolocalizetheweekday:SELECTELT(WEEKDAY('20040410')+1,'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag')

longversionwithmonth:SELECTDATE_FORMAT('20040410',CONCAT(ELT(WEEKDAY('20040410')+1,'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag'),',%d.',ELT(MONTH('20040410'),'Januar','Februar','Mrz','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'),'%Y'))>Samstag,10.April2004

sameforunixtimestamp:SELECTDATE_FORMAT(FROM_UNIXTIME(1081548000),CONCAT(ELT(WEEKDAY(FROM_UNIXTIME(1081548000))+1,'Mo','Di','Mi','Do','Fr','Sa','So'),',%d.',ELT(MONTH(FROM_UNIXTIME(1081548000)),'Jan.','Feb.','Mrz','April','Mai','Juni','Juli','Aug.','Sept.','Okt.','Nov.','Dez.'),'%Y'))>Sa,10.April2004

PostedbyPhilippePoelvoordeonApril3020045:50am [Delete][Edit]

Ihadtoqueryatableandretrieverowsthatwereaddedonlytoday,so:

selectidfrommy_tablewheretimestamp=date_format(CURRENT_TIMESTAMP(),'%Y%m%d000000')

startingwithMySQL4.0,youcouldalsousetheBETWEEN...ANDsyntax.Ifanyonehasabetterquerytodothat,letmeknow.

PostedbyMichaelMarcusonMay120042:41pm [Delete][Edit]

AfterreadingnumerousarticlesandpostsregardingconvertingbackandforthbetweenSQLdatetimeandVBscriptdatetime,Ioptedforthesimplestsolutionformydatabases.Isimplysavealldatetimevaluesinvarchar(20)fieldsandcalloneitherMySQLorVBscriptfunctionstogetdatetimevaluesorcheck/convertdatetimevalues.Forexample:

currentDT=CStr(cn.execute("SELECTNOW()").Fields(0).Value)

willfetchcurrentdatetimeintheSQLserver'sdatetimeformatandthenconvertittoastring.[Obviously,cnissetbySetcn=Server.CreateObject("ADODB.Connection")tocreatethedatabaseconnection,thenthedatabaseisopenedwithacn.open(parameters).]

Youcanthensavethisstringtoanappropriatefieldsuchas'flddate_added'whichisformattedasvarchar(20).

Whenretrievingtheflddate_addedvalue,youcanusethisVBscriptcodetocheckifthevalueisindeedadatetimevalueandconvertittothedatetimeformatoftheuser'scomputer"

ifIsDate(flddate_added)then=CDate(flddate_added)'converttouser'ssystemformatfordisplayusinguser'scodepageelse=flddate_added'justdisplaythestringendif

TheabovemethodsallowmetogetaroundalloftheissuesregardingVBscript'sdatetimedisplayformatdifferencesdependingonthesystemlocal.

PostedbyRayMorrisonJuly1520044:37pm [Delete][Edit]

PostedbyFilipWolak:

>Severaltimesihavecometoafollowngdate/timeproblem:>Inthetableiamstoringbothdateandtimeinformationinthedatetime>column.Querying,IwanttoreceiveCOUNTedresultsgroupedbydate,>andnotdateandtime....>SELECTsubstring(postdate,1,10)...

Ifit'saDATETIMEcolumnthansubstringisnotappropriateit'slogicallynonsensicalofcourse,andjusthappenstoworkinsomeversionofMySQLbecausetheDATETIMEhappenstoberepresentedbyastringinsomecontexts.BetterwouldbetotreattheDATETIMEasaDATETIMEratherthanasastring,whichwillworkinfutureversionsofMYSQLandinotherRDMS:SELECTDATE(postdate)...

PostedbyDavidLyononJuly1720044:12pm [Delete][Edit]

HereisanotherVB/ASPfunctionforconvertingDatesfromstandardtoMySQLformat.Cherisegaveaniceexampleabove,butithasextracomplexityduetotheuseofarraysandalsomaybepronedtouserinputerrors.

ThefollowingexamplewillworkbasedontheLocalizationsettingsoftheserveronwhichitisrun.Soitshouldn'tcarewhetherthedateisddmmyyyy,mm/dd/yy,



mm/dd/yyyy,mdyy,etc.Justmakesureyoupassitadatevaluethatisformattedcomplianttotheserver'slocalization.IfnecessaryuseVB'sCDate(strDateValue)beforepassingstrDateValuetothefunction.

YoucanalsoeasilymodifythisfunctiontodothesameforTimevalues,exceptyouuseHour,Minute,andSecondVBfunctions,anddelimitwithacolon(:)insteadofadash().

Hopethishelps!

FunctionfuncMySqlDate(dtmChangeDate)'CONVERTSLOCALIZEDDATEFORMAT(forexample:m/d/yy)TOMySQLFORMAT(yyyymmdd)DimstrTempYear,strTempMonth,strTempDaystrTempYear=Year(dtmChangeDate)strTempMonth=Month(dtmChangeDate)strTempDay=Day(dtmChangeDate)

ifLen(strTempYear)=2then'Y2KTEST19382037ADJUSTASNECESSARYifstrTempYear>=38thenstrTempYear="19"&strTempYearelsestrTempYear="20"&strTempYearendifendififstrTempMonth



seemstoworkwell.

PostedbyMartinAlgestenonSeptember920042:00pm [Delete][Edit]

>Severaltimesihavecometoafollowngdate/timeproblem:>Inthetableiamstoringbothdateandtimeinformationinthe>datetimecolumn.Querying,IwanttoreceiveCOUNTedresults>groupedbydate,andnotdateandtime.Icametotheeasy>solution:

Ineededaqueryforamoregeneralcasetodotimebasedreportingonarbitrarybig"slices"oftimestampeddata.

Mytablehasacolumn'timestamp'whichisoftype'datetime'.

Thefollowingmakes'120'secondbigslices

selectfrom_unixtime(unix_timestamp(timestamp)unix_timestamp(timestamp)%120)asslice,...groupbyslice

PostedbyDavidBerryonSeptember1720047:08pm [Delete][Edit]

Iwantedtofindthestartdate(Sunday)andtheenddate(Saturday)foranygivenweekwhenallIhadtogofromisanarbitrarydate(moreprecisely,thecurrentdate).SinceMySQLregistersSundayas1,andSaturdayas7,ifyouwishtoadjustthestartandendpointsonaweek,you'llhavetomodifythefollowingfunctioncallsappropriately,andchangetheintegers,or(asIhavedone)usevariables:

set@someday=curdate()set@weekstart=1//Sundayset@weekend=7//Saturday

endofweek:selectdate_add(@someday,interval@weekenddayofweek(@someday)day)

beginningofweek:selectdate_sub(@someday,intervaldayofweek(@someday)@weekstartday)

Ofcourse,Iusethesefunctionsinamorecomplexquerythatfiltersselectresultsfromatablewitha"datetime"field.Thisallowsmetofocusonweeklydata.Averyneatthingisbeingabletoreplace'curdate()'withadateat(theoretically)anypointintimeontheGregoriancalendar.

PostedbyJeffreyFriedlonOctober3120049:05am [Delete][Edit]

Thevaluereturnedby

UNIX_TIMESTAMP(NOW())

canbequiteunintuitiveduringthelasthourofdaylightsavingtimeinthefall,asitcanreturnatimestampthat'sanhouraheadofthecurrenttime.(Thedocsindicatethatthismaybe"fixed"from4.1.3,butIhavenottested.)

ThisisbecauseCSTrelatedinformationislostduringtheconversionbyNOW()fromthecurrenttimetoastring.Whenpresentedadatestringlike"2004103101:52:37"whichnamesatimethathappenedtwice(onceduringdaylightsavingtime,andagainanhourlaterinstandardtime),itdoesn'tknowwhichyouintendittobeinterpretedas.

Thedocsindicatethatfrom4.1.3,itusesthetimezoneineffectatthetimeoftheSELECT,whichimpliesthat

FROM_UNIXTIME("2004103101:52:37")

returnsadifferentvaluedependingonwhetheryouarecurrentlyunderdaylightsavingtimeornot.With4.1.2andbefore,itseemstoalwaysusestandardtime,andhencetheonehour"error"(whichisnotreallyanerror,butdamnunintuitivethatUNIX_TIMESTAMP(NOW())doesnotreturntheUNIX_TIMESTAMPfornow.

NotethatUNIX_TIMESTAMP()withoutargsdoesreturntheproperunixtimestampforthecurrenttime.

PostedbyShamuntohaonDecember18200410:45am [Delete][Edit]

Ifyouhaveatable1,and(fieldsdatewhichisvarchar(100)youcanalsoconvertitasdatetypelookthefollowingexample

mysql>selectstr_to_date(date,'%d/%m/%Y')asMydatefromtable1orderbyMydateDESC

++|Mydate|++|20041216||20041215||20041202||20041202||20041101||20041029||20041012||20041007||20040912||20040819||20040813||20040809||20040804||20040730||20040726||20040720||20040716||20040714|++

18rowsinset(0.00sec)

mysql>

PostedbyJohnRomanoonJanuary26200510:06pm [Delete][Edit]



IfyouneedtoEXTRACTtheQUARTERpriortov5.0tryCEILING(EXTRACT(MONTHFROMdate)/3)

PostedbyRobertChristiaanseonJanuary2720052:18pm [Delete][Edit]

CALCULATINGADATEUSINGAWEEKNUMBER

Ifyouwanttocalculatethedatehavingayear,adayoftheweekandaweeknumber(Let'ssayThursdayofweeknumber4in2005),youcancalculateitlikethis:

SELECTDATE_ADD('20050104',INTERVAL((41)*7+(4DATE_FORMAT('20050104','%w')))DAY)

InPHPitwouldbesomethinglikethis(whenweeksstartonMonday):

$Days=array('xx','ma','di','wo','do','vr','za','zo')$DayOfWeek=array_search($aDay,$Days)//getdayofweek(1=Monday)$Year=2005$Week=4

$query="SELECTDATE_ADD('".$Year."0104',INTERVAL((".$Week."1)*7+(".$DayOfWeek."DATE_FORMAT('".$Year."0104','%w')))DAY)"

January4thischosenasabase,becauseitisalwaysinweeknumber1.(January1stisnotnecessarelyinweek1!)

Youcantestitwiththis:



PostedbyErinQuickLaughlinonMarch29200512:49am [Delete][Edit]

TotakeChericeScharf'svbexampleonestepfurther,here'stheconversionfromvb'snowformatof'MM/DD/YYHH:MM:SSPM'to'YYYYMMDDHH:MM:SS'foreasyinsertiontothedatetimefield:

FunctionConvertInputDateTime(varDateTime)

If(Len(Trim(varDateTime))>0)ThenDateTimeArray=Split(CStr(varDateTime),"")

varDate=DateTimeArray(0)varTime=DateTimeArray(1)varAMPM=DateTimeArray(2)

If(Len(Trim(varDate))>0)ThenDateArray=Split(CStr(varDate),"/")

IFLen(Trim(DateArray(0)))



++|200411|200412|200413|200414|200415|...|etc...++

Hereisapieceofcodewhichwillmakesuchtable:



PostedbyOliverPereiraonJuly1920051:40pm [Delete][Edit]

ThedescriptionofFROM_DAYS(N)"GivenadaynumberN,returnsaDATEvalue"usestheterm"daynumber"withoutexplainingit.

ThedescriptionofTO_DAYS(date)"Givenadatedate,returnsadaynumber(thenumberofdayssinceyear0)"lowerdownthepageatleasttriestoexplaintheterm,butunsuccessfully.

Therearetwoproblemshere.Firstly,therewasnoyear0intheGregoriancalendar.Secondly,anumberofdayshastobecountedfromaday,notayear.Dotheymeanthebeginningofthe(nonexistent)year,ortheendofthe(nonexistent)year?Dononexistentyearsevenhavebeginningsandends?Someoneshouldamendthesedescriptions.

PostedbyksonAugust1220057:51am [Delete][Edit]

Here'sanotherquerytogetthenumberofmonthsbetweentwodates:selectperiod_diff(DATE_FORMAT(date1,'%Y%m'),DATE_FORMAT(date2,'%Y%m'))fromtablexy

PostedbyBobTerrellonAugust2220055:40pm [Delete][Edit]

Notethatthereiscurrentlynowaytogetthe'AM'or'PM'partofatimeonlyvalueusingthebuiltinfunctions.YoumustfirstconvertittoadatetimeandthenuseDATE_FORMAT('%p')orperformyourowncalculationsinyourapp.

PostedbyDeronMerandaonAugust3120058:33pm [Delete][Edit]

Ontransactionalconsistency...Concerningthefunctionswhichusetherealcurrenttime,suchasNOW(),themanualsays"Functionsthatreturnthecurrentdateortimeeachareevaluatedonlyonceperqueryatthestartofqueryexecution."

Notethoughthatthisdoesnotapplyacrossentiretransactions,asyoumayexpect.Thusatransactionlike:

STARTTRANSACTIONINSERTINTOEVENTSVALUES(NOW(),'A')INSERTINTOEVENTSVALUES(NOW(),'B')COMMIT

willresultinpotentiallytwodifferenttimesbeingrecordedforthetworecords.

PostedbycamerongreenonSeptember1620056:13am [Delete][Edit]

Ifyouneedthetypetobedynamicallytakenfromatable(thatiswhereyouhave"year","day","month"etcasacolumninthetable),hereisthebestwayIcouldworkouttodoit.Expandasnecessary:

SELECTset_date,unit_period,unit_multiplier,CASEWHENunit_period="month"THENDATE_SUB(set_date,INTERVALunit_multiplierMONTH)WHENunit_period="week"THENDATE_SUB(set_date,INTERVAL(unit_multiplier*7)DAY)WHENunit_period="year"THENDATE_SUB(set_date,INTERVALunit_multiplierYEAR)ELSEDATE_SUB(set_date,INTERVALunit_multiplierDAY)ENDFROMdates_table

PostedbyAndrzejSalamononSeptember2220051:27pm [Delete][Edit]

Returnsallrowsfromactualmonthtogiven@months.eg.ifyouwantgetallrowsin:

5monthsfromnow:(200509)5=(200504)allrowsfrom20050401to20050430

2monthsfromnow(200509)2=(200507)allrowsfrom20050701to20050731

SQLvariables,canbePHPvariableslike$months,$nextMonth,$begin,$end

set@months=1#changeonlythisvalue(monthsbackfromactualmonth)set@nextMonth=@months+1

set@begin=FROM_DAYS(TO_DAYS(LAST_DAY(DATE_SUB(NOW(),INTERVAL@nextMonthMONTH)))+1)

set@end=FROM_DAYS(TO_DAYS(LAST_DAY(DATE_SUB(NOW(),INTERVAL@monthsMONTH)))+1)

SELECTcols_u_wantFROMtbl_u_wantWHEREtimestampColBETWEEN@beginAND@end

It`smysolution.IfUhaveYourownpleaseemailme.Sorryformyenglish:)

PostedbyBryanDonovanonNovember1420059:22pm [Delete][Edit]

I'mnotsureifthisisthebestway,butitworkstogetthedateoftheMondayoftheweekofadate.Forexample,ifyouhaveadatetimecolumncalledstarttimeinatablecalledtest_events,youcouldselectthedistinctMondaysfromyourtableasfollows:

SELECTDISTINCT(STR_TO_DATE(CONCAT(YEARWEEK(starttime),'1'),'%x%v%w'))FROMtest_events

Hopefullythereisabetterway..

PostedbyRodolfoMaripanonNovember2920053:52pm [Delete][Edit]

Iwasusingmysqlv4andthedatewasinavarchardatatype,inordertochangethedatatypeinmysqlv5iusethefollowingcode:

updatessd_escondida.tactual_sag4setssd_escondida.tactual_sag4.Fecha=str_to_date(ssd_escondida.tactual_sag4.Fecha2,'%e/%m/%Y')

where:

ssd_escondida:databasetactual_sag4:isatableFecha:isadatetypeFecha2:isavarcharwhichcontainsadate,butisfrom01/01/2005to04/01/2005(withazeroatthebegining)

whyiused%einsteadof%d???theanswerisverysimple,thereisaproblemwithdehelpaboutstr_to_date:%d:representsthedays,butfrom0to31and...



%e:representsthedays,butfrom00to31.that'sthereasonwhywecannotuse:str_to_date('00/00/0000',%d/%m/%Y),wemustusestr_to_date('00/00/0000','%e/%m/%Y')Anotherwayinordertochangeastringlike:00/00/0000toadateistouse:str_to_date('00/00/0000','0%d/%m/%Y')

PostedbyReginaMullenonDecember3200510:25pm [Delete][Edit]

SimplemethodofconvertingdatesfromanyofMMDDYYYYMM/DD/YYYYMM.DD.YYYY(oldDate)toYYYYMMDD(addDate).Loaddateinastextandconvertinonegousing:

updatetablesetaddDate=CONCAT_WS('',RIGHT(oldDate,4),LEFT(oldDate,2),SUBSTRING(oldDate,4,2))

Caveat:makesureyourtextinputdoesn'thavespaces.

PostedbyHyperHackeronDecember26200510:54pm [Delete][Edit]

InMySQL4.0,andpossiblyothers,UNIX_TIMESTAMP()doesn'tworkwithdatesbefore1970.Thisquerydoesthesame,andworkswithanydatefromfromFri,13Dec190120:45:54toTue,19Jan203803:14:07.'date'isthenameoftheDATETIMEcolumnyouneedatimestampof.

SELECT(((TO_DAYS(date)*86400)+TIME_TO_SEC(date))(TO_DAYS("19700101")*86400))AStimestamp

Ifyou'reusingPHP,notethatdate()accountsforDSTandthusmayappeartoreturnincorrectresultsalso,don'tforgettoescapethequotesaround19700101.

PostedbyNoelAthaideonDecember2720058:23am [Delete][Edit]

Keyphrases:Birthdayreminder,selectdatesbetween

Thismightbeuseful.Ifyouhaveadatabasecontaining'name'and'birthday'(ascolumns)thenthefollowingquerywilllistthebirthdaysinthenext15days.(16tobemoreprecise:))

WhatIfounduniqueaboutthisproblemisthattheYEAR(ofbirth)willalwaysbedifferentandhenceonecannotsimplyuseaquerylike:

|SELECT*FROM`friends`WHERE|`birthday`>=CURDATE()|AND|`birthday`=DAYOFMONTH(CURDATE())|AND|DAYOFMONTH(`birthday`)



ORDERBYmonth,day,user_idASC

sorryforthestrangename,butthisisthewaymytablearenamed...

hopeyoulikeit

PostedbyStijnTasonJanuary2320064:07pm [Delete][Edit]

I'musingthisqueryforabirthdayreminder:

SELECT`geb_Geboorte`FROM`gebruikers`WHEREDAYOFYEAR(curdate())=dayofyear(`geb_Geboorte`)

Ichangetheyearofbirthdaytothecurrentyear.Sorryforthedutchtablenames.

PostedbyJohnL.onJanuary2520063:56pm [Delete][Edit]

Ittookmeabitoftimetofindhowtoselectdatabasedontimeperiods(suchasforquarterlyoryearlyreports).Youcanusegroupbymonth(DateTypeColumn).

exampletofindperiodictotals:SELECT[Year|Quarter|Month|Day](date)asPeriod,shipcountry,shipstate,shipcity,sum(products),sum(shipping),sum(tax)FROMproductsNATURALJOINshippingNATURALJOINtaxGROUPBYPeriod,shipcountry,shipstate,shipcity

moreherehttp://dev.mysql.com/doc/refman/5.0/en/dateandtimefunctions.html

Isupposeyoucouldalterthestartofquarterlyperiodsbydoingsomearithmeticonthe(date),butyoumighthavetodosomeconversions.

Postedby[namewithheld]onMarch620067:32pm [Delete][Edit]

TimearithmeticusingCURTIME()isquitewillingtotypeeverythingintointegersratherthanaddingandsubtractingseconds.Forexample,wherelog_timeisaTIMEcolumn

SELECTlog_timeASTimeFROMcall_logWHERElog_time>=(CURTIME()60)

willfetchallresultsfromthelast60seconds.However,

SELECTlog_timeASTimeFROMcall_logWHERElog_time>=(CURTIME()900)

willfetchallresultsfromthelast9minutes.900isinterpreted,notas900seconds(15minutes),butas9:00.Anhouris10000(1:00:00),not3600(36:00).

Ifyouwanttoaddseconds,usesomethinglikethefollowing(forthelasthour)

SELECTlog_timeASTimeFROMcall_logWHERElog_time>=(CURTIME()SEC_TO_TIME(3600))

PostedbyIssacGoldstandonMarch9200612:34am [Delete][Edit]

Ifyouhaveacolumnofdatevaluesandyouwanttocomparethedayportionofthemwithtoday'sdate,takinginmindshortermonthswhichmightnotcontainallthedatesinyourset(example,billingsystemsoranythingelsewhichneedstorunoneachrecordorrecordsetonagivendayofthemonth),youcantryoneofthese(replacing'20020430'withthedatefieldyou'recomparing):

SELECTDATE_FORMAT(CURDATE()INTERVAL1MONTH,CONCAT('%Y%m',DAY('20020430')))+INTERVAL1MONTH

Thistendsto"rounddown"onmissingdaysforexamplefordatesendingin30,thiswilltranslatetofeb28(infebruary).

PostedbyMarkoKruustkonApril520062:19pm [Delete][Edit]

Inreplyto"DavidBerryonSeptember1720049:08pm"

Problem:Tofindweekstartandenddatewithuserspecifiedstartoftheweekdayanduserspecifieddateforwhichtheweekistobefound.

David'ssolutiondoesnotworkwithuserspecifiedweekstartandend.Itonlyworkswithnormalweekwhichis1and7asstartandendcorrespondingly.

AsIneededdifferentstartingdayforweekthanSundayorMondayfortimesheetcalculations,Ihadtocomeupwithworkingsolution:

...

date_sub(t.date,intervalif(dayofweek(t.date)$weekStartingDay>=0,dayofweek(t.date)$weekStartingDay,dayofweek(t.date)$weekStartingDay+7)day)week_start

...

date_sub(t.date,intervalif(dayofweek(t.date)$weekStartingDay>=0,dayofweek(t.date)$weekStartingDay,dayofweek(t.date)$weekStartingDay+7)6day)week_end

...

Thissolutionworksfineforme,atleastatthemomenttillIfindsomebuginit:)

Postedby[namewithheld]onApril1420063:57am [Delete][Edit]

UsethistofindthedateofthelastFriday.Pleaseletmeknowifthereisamoreefficientwayofdoingthis.

selectif(DATE_FORMAT(curdate(),'%w')>4,date_sub(curdate(),INTERVALDATE_FORMAT(curdate(),'%w')5DAY),date_sub(curdate(),INTERVALDATE_FORMAT(curdate(),'%w')+2DAY))

PostedbyHorstSchirmeieronApril17200611:16pm [Delete][Edit]

Justanotherexampleonhowtofigureouthowmanydaysareuntilsomebirthdate(inordertodoarangequery,orgetthe"next"birthday):



SELECTname,birthday,IF(DAYOFYEAR(birthday)>=DAYOFYEAR(NOW()),DAYOFYEAR(birthday)DAYOFYEAR(NOW()),DAYOFYEAR(birthday)DAYOFYEAR(NOW())+DAYOFYEAR(CONCAT(YEAR(NOW()),'1231')))ASdistanceFROMbirthdates

The+DAYOFYEAR(CONCAT(YEAR(NOW()),'1231'))(whichis366or365,dependingonwhetherwe'reinaleapyearornot)takescareoftheNewYear'sEvewraparound.

YoucouldaddWHEREdistance



Togetthelastdayofthecurrentmonth:

SELECT(SUBDATE(ADDDATE(CURDATE(),INTERVAL1MONTH),INTERVALDAYOFMONTH(CURDATE())DAY))ASLastDayOfTheMonth

Thiswillgiveyouthefirstdayofthemonth.

mysql>SELECT(SUBDATE(ADDDATE(CURDATE(),INTERVAL1MONTH),INTERVALDAYOFMONTH(CURDATE())DAY))ASLastDayOfTheMonth

++|LastDayOfTheMonth|++|20060731|++

1rowinset

Hopethishelps!

PostedbyJordanGrayonAugust1200611:17am [Delete][Edit]

Thisfunctionwillreturnthedifferencebetweentwodatesasastring,intheformat"Yyear[s],Mmonth[s],Dday[s]"(pluralisationasappropriate):

|CREATEFUNCTIONgetDateDifferenceString(date1DATE,date2DATE)RETURNSVARCHAR(30)|RETURNCONCAT(|/*Yearsbetween*/|@years:=TIMESTAMPDIFF(YEAR,date1,date2),|IF(@years=1,'year,','years,'),|/*Monthsbetween*/|@months:=TIMESTAMPDIFF(MONTH,DATE_ADD(date1,INTERVAL@yearsYEAR),date2),|IF(@months=1,'month,','months,'),|/*Daysbetween*/|@days:=TIMESTAMPDIFF(DAY,DATE_ADD(date1,INTERVAL@years*12+@monthsMONTH),date2),|IF(@days=1,'day','days')|)|;

Ittookawhiletoworkthisoneout,soIhopethismightsavesomeoneelsethebother.

PostedbyDaevidVincentonAugust420067:14am [Delete][Edit]

I'mnotsurewhyHorstSchirmeierdidthatverycomplexbirthdateequation.Seemstomeyoucouldjustdo:

SET@DOYNOW=DAYOFYEAR(CURDATE())

SELECT(DAYOFYEAR(birthdate)@DOYNOW)ASbirthdays,birthdate,@DOYNOW,CURDATE()FROMusersWHEREbirthdateISNOTNULL

thenifbirthdays==0,it'sthatpersonsbirthday,otherwiseyouknowifthebirthdayisinthefuturebyhowmanydays,orifyoumisseditandhowmanybeersyouowethem...

(althoughthemissed/negativedaysseemstobeoff)

+++++|birthdays|birthdate|@DOYNOW|CURDATE()|+++++|83|19691026|216|20060804||3|19810807|216|20060804||1|19720802|216|20060804||0|19460804|216|20060804||151|19760305|216|20060804|+++++

Shouldn'tthat1be2?AmImissingsomethingobvious?

IfIdo"SELECTDATEDIFF('20060801',CURDATE())"Iget2asIexpect.

So,Iguesstherealsolutionistousethis:

SET@YEAR=CONCAT(EXTRACT(YEARFROMCURDATE()),'')

SELECTDATEDIFF(CONCAT(@YEAR,DATE_FORMAT(birthdate,'%m%d')),CURDATE())ASbirthdays,birthdate,CURDATE()FROMusersWHEREbirthdateISNOTNULL

++++|birthdays|birthdate|CURDATE()|++++|83|19691026|20060804||3|19810807|20060804||2|19720802|20060804||0|19460804|20060804||152|19760305|20060804|++++

Bytheway,ifyou'reusingPHPorsomeotherscriptinglanguage,youcangetridofthe@YEARstuffandjustdo:

DATEDIFF(DATE_FORMAT(birthdate,'".date('Y')."%m%d'),CURDATE())ASbirthdays



Thefollowingreturns1fortheweekbetween20031229and20040104:

|SELECT|WEEK('20031229',3),|WEEK('20040104',3)

PostedbyMartinMinkaonJanuary2520075:23pm [Delete][Edit]

Icreatedthisfunctiontocalculate"workingday"differenceoftwodates.Ifyouhavetablewithlistofholidaysyoumayuncommentpartinthisfunctiontoexcludedaysofholidaysalso.

DELIMITER$$

DROPFUNCTIONIFEXISTS`workdaydiff`$$

CREATEDEFINER=`root`@`%`FUNCTION`workdaydiff`(bdate,adate)RETURNSint(11)DETERMINISTICCOMMENT'workingdaydifferencefor2dates'BEGINDECLAREfreedaysintSETfreedays=0

SET@x=DATEDIFF(b,a)IF@x5THENSET@w1=0ELSESET@w1=6@w1ENDIF/*daysinlastweek*/SET@wx2=WEEKDAY(b)+1SET@w2=@wx2IF@w2>5THENSET@w2=5ENDIF/*summary*/SET@weeks=(@x@wx1@wx2)/7SET@noweekends=(@weeks*5)+@w1+@w2/*UncomentthisifyouwantexcludealsohollidaysSELECTcount(*)INTOfreedaysFROMhollidayWHEREd_dayBETWEENaANDbANDWEEKDAY(d_day)=TIME_TO_SEC(first_date),TIME_TO_SEC(last_date)TIME_TO_SEC(first_date),86400+(TIME_TO_SEC(last_date)TIME_TO_SEC(first_date)))FROMtable

Thiswillreturnthetimebetweenthelast_dateandthefirstdate,takingintoaccountthevalueswherefirst_dateandlast_dateareondifferentdays.

PostedbyWillJaspersonMay220073:58pm [Delete][Edit]

NOTE:DateFormatcannotbeusedtoformataTimeDiffcalculation.

Example:SELECTDATE_FORMAT(TIMEDIFF(àppointment_start`,àppointment_end`),'%H:%i:%s'))ASdurationFROMappointments

CorrectSyntax:SELECTTIMEDIFF(àppointment_start`,àppointment_end`)ASdurationFROMappointments

(TestedonMySQL4.1.20)

Postedby[namewithheld]onMay3120075:11pm [Delete][Edit]

Mypickonbirthdaysremainders:

selectdate_format(date,"%d/%m"),DAYOFYEAR(CURDATE()),DAYOFYEAR(date)fromtablewhereDAYOFYEAR(date)betweenDAYOFYEAR(CURDATE())15andDAYOFYEAR(CURDATE())+15orderbydate_format(date,"%d/%m/%Y")

GabrielRegulyhttp://ppgr.com.br

PostedbyEjectDisconJune11200710:53am [Delete][Edit]

AsmentionedaboveSTR_TO_DATE()isavailableasofMySQL4.1.1.



ThisfunctioncanbeusefulifyouaregroupingrowsbyWeekofYearandthenwanttoproduceatablewith"WeekCommencing"asthepointsonyourXaxis.

Sowhatdoyoudoifyou'recodeshopisusingpre4.1?

Here'swhatIdid.Ihaveatableofeventshappeningonadatetime.Iwantedaneventcountbyweekwiththedateofthestartoftheweek,assumingtheweekstartsMonday.

Ihaveexploitedthegroupbyfunctiontoextracttheminimumdatetimevaluewhichinmycaseisguaranteedtobeatleastoncedaily.

Thiswillnotworkifyourdataisnotbeinginjecteddaily!

selectcount(*)as'count',date_format(min(added_on),'%Y%M%d')as'weekcommencing',date_format(added_on,'%Y%u')as'week'fromsystemwhereadded_on>='20070516'groupbyweekorderby3desc

++++|count|weekcommencing|week|++++|88|2007June04|200723||276|2007May28|200722||275|2007May21|200721||160|2007May16|200720|++++

Hopethatsusefulforsomeone!

ImranChaudhry

PostedbyAndrewHollowayonAugust820076:01pm [Delete][Edit]

Duetoabuginmysqlversionspriorto5.0.36,thereisaproblemwhenperformingmultipleSEC_TO_TIMEconversionsandthereareintermediatenullvalues.Itwillturntheresultsafterthefirstnullintonullvalues.

Forexample:ifyouhaveatable(date_diff)asfollows:

|id|time1|time2|++++|1|9:30:05|(null)||2|10:05:07|10:05:17||3|11:00:03|11:01:00||4|12:05:11|(null)|

andyourunaquery:

selectsec_to_time(time_to_sec(time1)time_to_sec(time2))asdifffromdate_diff

Youwillseeresultsasso:

|diff|++|00:00:00||(null)||(null)||(null)|

Andaqueryexcludingid1willresultin:

|diff|++|00:00:10||00:00:57||00:00:00|

Aworkaroundwouldbetouseacasestatement:

selectcasewhenisnull(time_to_sec(time1)time_to_sec(time2))thennullelsesec_to_time(time_to_sec(time1)time_to_sec(time2))endasdifffromdate_diff

Or,upgradetoamysqlversionincludingthisbugfix(#25643).

PostedbyMarcelBrouilletonAugust1620071:37pm [Delete][Edit]

Todisplaythedateofthemondayprecedingagivenday,BryanDonovansuggestedthefollowing:>SELECTDISTINCT(STR_TO_DATE(CONCAT(YEARWEEK(starttime),'1'),'%x%v%w'))>FROMtest_events

Infact,youneedtobeconsistentinthetypeofweeksyouuse.TheabovewouldtellyouthattheMondayJuly162007ispartoftheweekstarting...MondayJuly92007!Thiscomesfromweekdefinitionambiguities(seeWEEK()above).Topreventthis,specifythemodeonYEARWEEKtobeMondaybased:payattentiontotheextraparametertothefunctionYEARWEEK)below



++|DATE_FORMAT('20070716',"%W")|++|Monday|++++|STR_TO_DATE(CONCAT(YEARWEEK('20070716'),'1'),'%x%v%w')|++|20070709|++++|STR_TO_DATE(CONCAT(YEARWEEK('20070716',1),'1'),'%x%v%w')|++|20070716|++

Onewouldexpectthatdefault_week_formathasthesameeffectonWEEKDAY()thanithasonWEEK()andthatsettingthisvariableto1or3wouldsuffice.No,asofMysql5.0.26itseemstohavenoeffect:

SETdefault_week_format=1SELECTSTR_TO_DATE(CONCAT(YEARWEEK('20070716'),'1'),'%x%v%w')

++|20070709|++

PostedbyDaveHoldenonSeptember6200712:43am [Delete][Edit]

ANNIVERSARIESARETRICKY!

ForversionsofMySQLpreviousto4.1itisquitedifficulttodetermineifadatefield'sanniversarydatefallswithinaspecifieddaterange.Dayofyearcalculationsfailbecauseofleapyearsandthepossibilitythatthedaterangeyouhavespecifiedspansayearboundary.HereiswhatIhavecomeupwith.HopefullyitcansavesomeonetheheadacheandGoogleFeverIhadtogothroughtocomeupwithit.

**SOMESELECTSTATEMENT...**WHERE((month(Date)BETWEENmonth('[MyStartDate]')ANDmonth('[MyEndDate]')ANDmonth('[MyStartDate]')=dayofmonth('[MyStartDate]')ANDmonth(Date)=month('[MyStartDate]')ORdayofmonth(Date)month('[MyStartDate]')ANDmonth(date)month('[MyEndDate]')AND(dayofmonth(Date)>=dayofmonth('[MyStartDate]')ANDmonth(Date)=month('[MyStartDate]')ORdayofmonth(Date)month('[MyStartDate]')ORmonth(Date)



Birthdayreminder

Thenextbirthday(includingtoday!)iswhenthepersonis1yearolderthanhe/shewasyesterday.SoIuse

mysql>selectname,birthday,adddate(birthday,intervaltimestampdiff(year,adddate(birthday,interval1day),current_date)+1year)asnext_bdfrompersonorderbynext_bd

++++|name|birthday|next_bd|++++|FooToday|19701012|20071012||Bar1|19901225|20071225||Bar2|20000125|20080125||Foo|19800229|20080229|++++

4rowsinset(0.00sec)

mysql>selectname,birthday,adddate(birthday,intervaltimestampdiff(year,adddate(birthday,interval1day),'20081012')+1year)asnext_bdfrompersonorderbynext_bd

++++|name|birthday|next_bd|++++|FooToday|19701012|20081012||Bar1|19901225|20081225||Bar2|20000125|20090125||Foo|19800229|20090228|++++

4rowsinset(0.00sec)

Asyoucansee,thisisalsoworkingforleapyears.

BTW:Is"20080229"plus1year"really"20090228"?)

PostedbyAaronDavidsononDecember620074:05pm [Delete][Edit]

Totestifadateisavaliddate:

SET@testdate="20070229"SELECTIF(@testdate=DATE_ADD(DATE_ADD(@testdate,INTERVAL1DAY),INTERVAL1DAY),TRUE,FALSE)

Returns0(false)

SET@testdate="20080229"SELECTIF(@testdate=DATE_ADD(DATE_ADD(@testdate,INTERVAL1DAY),INTERVAL1DAY),TRUE,FALSE)

Returns1(true)

PostedbyMohamedInfiyazZafferKhalidonDecember1220071:45pm [Delete][Edit]

Findingadatebeforeagivennumberofdays

Often,forcertainapplications,weneedtosubtractsomedaysfromagivendatetofindanotherdate.Forexample,inalibrary,weneedtogo21daysbehindfromthecurrentdateandlistthebooksthatweretakenbeforethatdate.Thiswouldbetheoverduelist.

HereisasimpleSQLstatement.Thistypeofuseispracticallyessentialtomostapps.

SELECTSUBDATE('20071212',INTERVAL3DAY)

Thelineabovewillgivetheanswer2007129.Toexplainitfurther,thefunctionSUBDATEreturnsadateaftersubtractingaspecifiedduration.Inourexample,thedurationis3days.ToindicatethatwearedealingwithDAYS,weusethetermINTERVAL.Sothefunctionabovecanbeexplainedaswhatisthedate,threedaysbeforetoday?

Nowifyouwanttofindthedate20daysbeforeTODAYorthecurrentsystemdate,thisiswhatyoushoulddo:

SELECTSUBDATE(CURRENT_DATE,INTERVAL20DAY)

Inthestatementabove,weareusingoneoftheMySQLconstantsthatholdthecurrentdateontheserver.Wecount20daysbackandgettheanswerasaresult.

Happycoding.

Khalid(itsols)

PostedbyMarcusMatosonJanuary820081:02pm [Delete][Edit]

Important:ItshouldbeknownthatMySQL>=5.0.42silentlychangesthebehaviorofcomparingaDATEcolumntoNOW().

See:http://bugs.mysql.com/bug.php?id=28929

ThisbreaksmanythingssincenowqueriesusingWHEREdatecol=NOW()willreturnNULLwherepreviouslyitwouldreturnresults.

UseCURDATE()instead.I'mhavingtogobackthroughyearsofcodetofixthis.

PostedbyTomaszKopeconJanuary1720081:41pm [Delete][Edit]

referingtothedocumentsectiontellingaboutintervalsindate_add/date_subfunctionsdescribingexample:SELECTDATE_ADD('19990101',INTERVAL6/4HOUR_MINUTE)Onecanavoidfallingintotrapsimplyenclosinginterval6/4intoquotationmarkssothequerywillbelooklikethis:SELECTDATE_ADD('19990101',INTERVAL'6/4'HOUR_MINUTE)Thiscasetheintervalwillbeconsideredasregularstringandnocalculationwillbeperformedbeforepassingasargument

PostedbyMohamedMahironJanuary2320086:31am [Delete][Edit]



EarlierIusedtimediffinwhereclauseworkedperfectlybutdon'tknowwhyitisnotworkingnowespeciallyaftercpuusageexceedsin*intwodifferentsharedhosting*

querylike

select*fromtablewheretimediff(sysdate(),datetimecolumn)



"

DELIMITER$$

DROPFUNCTIONIFEXISTS`effectiveworkdays`$$

CREATEDEFINER=`myUserName`@`myHost`FUNCTION`effectiveworkdays`(STARTDATEdate,ENDDATEdate,WORKABLE1integer,WORKABLE2integer,WORKABLE3integer,WORKABLE4integer,WORKABLE5integer,WORKABLE6integer,WORKABLE7integer)RETURNSint(11)DETERMINISTICCOMMENT'effectiveworkingdayfor2dates'BEGINDECLAREDAYCOUNTintDECLAREHOLIDAYCOUNT,HOLIDAYSintDECLARESTARTDAYSintDECLAREENDDAYSint

SETDAYCOUNT=0SETHOLIDAYCOUNT=0SETHOLIDAYS=0SETSTARTDAYS=TO_DAYS(STARTDATE)1SETENDDAYS=TO_DAYS(ENDDATE)

IF(STARTDAYSgetRequestParameter("id"))



$tmp=EmployeePeer::doSelect($c)$employee=$tmp[0]

$schedule=$employee>getEmploymentContract()>getWorkSchedule()

$workableFirst=$schedule>getWorkableFirst()==1?1:0$workableSecond=$schedule>getWorkableSecond()==1?1:0$workableThird=$schedule>getWorkableThird()==1?1:0$workableFourth=$schedule>getWorkableFourth()==1?1:0$workableFifth=$schedule>getWorkableFifth()==1?1:0$workableSixth=$schedule>getWorkableSixth()==1?1:0$workableSeventh=$schedule>getWorkableSeventh()==1?1:0

$query="SELECTsum(effectiveworkdays2(vrs.confirmed_from,vrs.confirmed_to,$workableFirst,$workableSecond,$workableThird,$workableFourth,$workableFifth,$workableSixth,$workableSeventh))ASsumFROMapp_vacation_responsesvrsLEFTJOINapp_vacation_requestsvrqONvrs.request_id=vrq.vacation_idWHEREvrq.requested_by=".$employee_id."ANDvrs.confirmed_from>='".date('Y')."0101'ANDvrs.confirmed_to



Notethatthe"yesterday"checkoccursbeforethe"hours"check.Thisisintentionalsothathourswillonlybeshownifthemodificationoccurredduringthecurrentday.

Hopethisisusefulforsomeoneelse!

PostedbySimonStefanonJuly3120086:29am [Delete][Edit]

Hereisaselectionforabirthdayreminderwith2daysbefore(INTERVAL2DAY):

selectid,angajat_id,data_nasterefrominfo_angajatwhereDATE_ADD(date(concat(YEAR(data_nastere),"",MONTH(NOW()),"",DAYOFMONTH(NOW()))),INTERVAL2DAY)=data_nastere

PostedbyChandruNonAugust14200811:59am [Delete][Edit]

Tofindthestartandendofamonthyoucanusethefollowingqueries:Lastdayofcurrentmonth:SELECTLAST_DAY(now())LastdayofPreviousmonthSELECTLAST_DAY(now()interval1month)Firstdayofcurrentmonth:SELECTconcat(date_format(LAST_DAY(now()),'%Y%m'),'01')Firstdayofpreviousmonth:SELECTconcat(date_format(LAST_DAY(now()interval1month),'%Y%m'),'01')

PostedbyMatiasThayeronOctober920082:22pm [Delete][Edit]

Hi,hereisaMySQL(5)functiontovalidateadate,informatYYYYMMDD(example:"selectisDate(20080229)").Thisfunctioninclude"leapyear"validation.Itrytosimulate"isdate()"functionofSqlServer.

DELIMITER$$CREATEFUNCTION`isDate`(svarchar(100))RETURNSdecimal(8)BEGIN

/*validafechasuponiendoqueesingresadocomo"20081228"(largo8)*/declaretpintdeclarebisiestoint

sets=trim(s)/*realizamosvalidacionesgenerales*/iftrim(s)=0andlength(trim(s))8thenreturn0/*fechainvalida*/endif

ifsubstr(s,1,4)notbetween0and9999thenreturn0/*annoinvalido*/endif

ifsubstr(s,5,2)notbetween1and12thenreturn0/*mesinvalido*/endif

/*vemossiesbisiestoelano*/setbisiesto=0ifsubstr(s,1,4)mod400=0thensetbisiesto=1elseifsubstr(s,1,4)mod100=0thensetbisiesto=0elseifsubstr(s,1,4)mod4=0thensetbisiesto=1elsesetbisiesto=0endif

settp=(casewhensubstr(s,7,2)0then1else0end)

returntp

END$$

PostedbyParisAlexonNovember19200812:37am [Delete][Edit]

I'vealwaysfinditusefultobeabletofindoutthenumberofrecordscreatedonamonthlybasis.ThisSELECTstatementdoesthejobbyformattingadatefieldusingDATE_FORMAT()andgroupittoseedatabytheyearandmonth.

SELECTDATE_FORMAT(creationDate,'%Y%m')ASmonth,COUNT(*)AStotalFROMmyTableGROUPBYmonthORDERBYtotalDESCLIMIT5

+++|month|total|+++|200607|485||200608|179||200810|96||200806|89||200801|84|+++



5rowsinset(0.00sec)

TheORDERBYandLIMITisoptional,tofurtherfiltertheselectstatement.

Regards,Alexhttp://www.loveromehotel.com/

PostedbyJooMarquesGomesonFebruary620093:46pm [Delete][Edit]

Getfirstdayofcurrentweek.WhenfirstdayisaSunday:

selectdate_sub(curdate(),intervaldayofweek(curdate())1day)

Joao

PostedbyLoretoParisionFebruary8200911:44am [Delete][Edit]

Ifoundfunctionstr_to_date()veryusefulwhenhaving"humanreadable"dateformatsandyouwanttousetheORDERBYclausetosortthetablebythosefields:

Supposedwehaveafields'published_date'likethis:

SELECT[,]published_date[,]

++|published_date|++|Fri,23Jan200900:00:000800||Mon,26Jan200902:21:090800||Fri,23Jan200916:00:000800||Thu,22Jan200915:00:000800||Thu,29Jan200902:00:270800|++

NowwetrytoORDERBYpublished_dateDESC,resultingin:

SELECT[,]published_date[,]ORDERBYpublished_dateDESC[]

++|published_date|++|Wed,21Jan200918:30:000800||Wed,21Jan200911:30:360800||Tue,27Jan200900:09:460800||Tue,20Jan200916:00:000800||Tue,13Jan200916:00:000800|++

Asyoucanseethesetisnotwellsorted,Sosimplyusethestr_to_datefunwiththesyntax:

SELECT[,]str_to_date(published_date,'%a,%d%b%Y%H:%i:%s')asmy_published_date[,]ORDERBYmy_published_dateDESC[]

Sowe'llhave:

++|my_published_date|++|2009013011:39:04||2009012910:26:51||2009012902:00:27||2009012700:09:46||2009012608:10:45|++

andthesetisnowsortedwell.

PostedbyStevenCopleyonMarch2620091:00am [Delete][Edit]

Thiswillgiveweekdays(everythingminusweekends)between2dates:

SELECT(floor(datediff('20090111','20090101')/7)*5)+CASEdayofweek('20090101')WHEN1THENmod(datediff('20090111','20090101'),7)2WHEN7THENmod(datediff('20090111','20090101'),7)1ELSELEAST(7dayofweek('20090101'),mod(datediff('20090111','20090101'),7))END

PostedbyJoshuaLeboonMay1920097:32pm [Delete][Edit]

Anotherwaytofindthelastdayofamonth(withoutLAST_DAY),givenadatewithinthatmonth:

selectdate_sub(concat(date_format(date_add(curdate(),interval1month),'%Y%m'),'01'),INTERVAL1DAY)

orforaspecificday:

selectdate_sub(concat(date_format(date_add('20080218',interval1month),'%Y%m'),'01'),INTERVAL1DAY)



walkstothefirstofthefollowingmonth,thensubtracts1day

PostedbyDanielBersteinonJune2320094:01pm [Delete][Edit]

TofindfuturedateinXbusinessdays,tookmeawhilebuthereitiswithoutusingstoreprocedures,insteadIcomputethenumberofweekendsbetweentheinitialdateanddate+Xdays,adjustfortheweekenddays.Itsimplementedasaderivedtabletoeasyjoinswithrealtables.

/*Theinitialdatefromwhentostartcounting*/SET@start:=NOW()/*Numberofbusinessdays*/SET@days:=4/*Thequery*/SELECTcalendar.`Result`FROM(/*Ifinitialdateisonaweekend,adjusttofirstbusinessday*/SELECT@start:=@start+INTERVALCASEWHENDAYOFWEEK(@start)=7THEN2WHENDAYOFWEEK(@start)=1THEN1ELSE0ENDDAY`First`,@days`Days`,/*Numberofestimatedbusinessweeks*/@weekends:=DATEDIFF(@start+INTERVAL@daysDAY,@start)DIV5`Weekends`,/*Estimatedtargetdatebasednumberofweekends*/@guess:=@start+INTERVAL@days+2*@weekendsDAY`Guess`,/*Adjustresultdateifitfallsonaweekend*/IF(DAYOFWEEK(@guess)=1,@guess+INTERVAL1DAY,IF(DAYOFWEEK(@guess)=7,@guess+INTERVAL+2DAY,@guess))`Result`)calendar

PostedbyMartinStjernholmonAugust320092:28pm [Delete][Edit]

ToexpandabitonthenotebyJeffreyFriedlonOctober3120049:05am:

ThemanualstatesthatFROM_UNIXTIME(UNIX_TIMESTAMP(...))doesn'tmapbacktothesameformatteddate.Whatalsoshouldbestatedisthatthereverseisnottrueeither.

UNIX_TIMESTAMP(ts)wheretsisaTIMESTAMPcolumnreturnstheunixtimestampstoredinitwithoutconversionto/fromaformatteddatestring.ThatavoidstroublewithtimezonesandDSToverlapsetc.

However,whenthecolumntsissetthroughFROM_UNIXTIME(n),it(apparently)formatstheunixtimestampasastringwhichisthenparsedbacktoatimestampagain.Theformattingandtheparsingisdonewiththesametimezone,sothetimezoneoffsetsgenerallycancelthemselvesout.TheexceptionisifthetimezoneusesDSTandthetimestampisintheoverlappinghourinthefallwhengoingfromsummertimetonormaltime.

E.g.iftheactivetimezoneontheconnectionisCentralEuropeanTime,whichusesDST,thensetting1130630400(Sun30Oct20052:00:00CEST)through

INSERTINTOfooSETts=FROM_UNIXTIME(1130630400)

actuallysetsthetscolumnto1130634000(Sun30Oct20052:00:00CET),i.eonehourlater.

Theonlywayaroundthatproblemisapparentlytoensurethatthetimezoneusedontheconnectionisonewhichdoesn'tuseDST.E.g.UTCisareasonablechoice,whichcanbesetontheconnectionthrough"SETtime_zone='+00:00'".

PostedbyWernerKremeronAugust420093:06pm [Delete][Edit]

IimplementedawellknownalgorithmfromanEnglishstandardsorganisation(I'veforgottenexactlywho).

forthecalculateeastersundayiusedfollowingfunction:

DELIMITER$$

USE`pczeitdb`$$

DROPFUNCTIONIFEXISTS`fneastern`$$

CREATEDEFINER=`wkroot`@`%`FUNCTION`fneastern`(iYearINT)RETURNSDATEDETERMINISTICBEGINSET@iD=0,@iE=0,@iQ=0,@iMonth=0,@iDay=0

SET@iD=25511*(iYear%19)SET@iD=IF(@iD>50,(@iD21)%30+21,@iD)SET@iD=@iDIF(@iD>48,1,0)SET@iE=(iYear+FLOOR(iYear/4)+@iD+1)%7SET@iQ=@iD+7@iEIF@iQ



ThefollowingwilloutputtheproperfullhouroffsetfromUTC:

SELECTTIME_FORMAT(NOW()UTC_TIMESTAMP(),'%H%i')AStz_offset

PostedbyJohnDoeonDecember21200911:56am [Delete][Edit]

ThisisaSQLstatementtolistallthepeoplewhosebirthdayiscomingupinthenextcoupleofweeks,payingattentiontoyear'send:

SET@YEAR=EXTRACT(YEARFROMCURDATE())

SELECTnameFROMmytableWHEREDATEDIFF(DATE_FORMAT(birthdate,CONCAT(@YEAR,"%m%d")),CURDATE())BETWEEN0AND15ORDATEDIFF(DATE_FORMAT(birthdate,CONCAT(@YEAR+1,"%m%d")),CURDATE())BETWEEN0AND15

PostedbyRickWellmanonJanuary520101:03am [Delete][Edit]

MuchappreciationtoStevenCopley'spostconcerningweekdays[sincethatiswhatIwaslookingfor]butIthinkheusedthedayofweek()functionwherehemeanttousetheweekday()functionintheLEAST()clause.i.e.

Thiswillgiveweekdays(everythingminusweekends)between2dates:

SELECT(floor(datediff('20090111','20090101')/7)*5)+CASEdayofweek('20090101')WHEN1THENmod(datediff('20090111','20090101'),7)2WHEN7THENmod(datediff('20090111','20090101'),7)1ELSELEAST(7weekday('20090101'),mod(datediff('20090111','20090101'),7))END

PostedbyRobertEiseleonJanuary1520104:15pm [Delete][Edit]

Idiscussedthetopicofagecalculationtwoweeksbeforeonmyblog:http://www.xarg.org/2009/12/agecalculationwithmysql/

IfoundaverysmartsolutiontocalculatetheagefromaDATEvalue:

CREATEFUNCTIONgetage(BDAYDATE)RETURNSTINYINTUNSIGNEDRETURNYEAR(FROM_DAYS(DATEDIFF(NOW(),BDAY)))

Tocalculatesomeonesnextbirthday,Iwroteasimilarfunction:

CREATEDFUNCTIONnextbday(bdayDATE)RETURNSDATERETURNDATE_ADD(bday,INTERVALYEAR(FROM_DAYS(DATEDIFF(NOW(),bday)1))+1YEAR)

PostedbyMichaelCunninghamonJanuary2420104:20pm [Delete][Edit]

Ipersonallygotstuck,tryingtofeedVBScriptdatesbackintoMySQLastheODBCDriverconvertstheMySQLdatestoVBScript'sversionautomatically.

ThisisaonelinersolutionforeveryoneusingVBScript(ASP),thatneedstofeedaVBdateintoMySQL.

Input:REQmodinanydateformatthatVBScriptunderstands(1/1/197012:00:01AM)

REQmod=year(REQmod)&""&right("0"&month(REQmod),2)&""&right("0"&day(REQmod),2)&""&right("0"&Hour(REQmod),2)&":"&right("0"&Minute(REQmod),2)&":"&right("0"&Second(REQmod),2)

Output:REQmodwillbestrictlyinMySQLFormat(1970010100:00:01)

PostedbyD.MeaneaonMay420109:37pm [Delete][Edit]

TwothingstokeepinmindwhenusingtheTIMESTAMPDIFFfunction:

1.Thefunctionreturnsatruncatedintegerofthespecifiedunit.ItdoesNOTusetheunittodeterminetheprecisionofthedifferencecalculation.Forexample,youmightexpectSELECTTIMESTAMPDIFF(MONTH,'20100331','20100430')toreturn1,asthedifferencebetween'201003'and'201004'.However,becausethedifference(includingthedays)isnotquiteafullmonth,itreturns0,not1.

2.Inversion5.0,theunityouspecifydetermineswhetherthecalculationincludesthetimeelements.WhenusingtheYEARorMONTHunits,thefunctionusesthedateelements(year,monthandday)inthecomparison,butignoresthetimeelements.Forexample,SELECTTIMESTAMPDIFF(MONTH,'2010030100:00:00','2010040100:00:00')andSELECTTIMESTAMPDIFF(MONTH,'2010030123:59:59','2010040100:00:00')bothreturn1,whileSELECTTIMESTAMPDIFF(MONTH,'2010030200:00:00','2010040100:00:00')returns0.

However,ifyouuseDAYastheunit,alldateandtimeelementsareincludedinthecalculation.Thus,SELECTTIMESTAMPDIFF(DAY,'2010032900:00:00','2010033000:00:00')returns1,whileSELECTTIMESTAMPDIFF(DAY,'2010032900:00:01','2010033000:00:00')returns0.

Inversion5.1,thefunctionincludesthetimeelementsinallcalculations.(Mytestingwasdoneinversions5.0.75and5.1.30)

Thisfunctionworksfineforcalculationsonthetimepartofatimestampbutforworkingwiththedatepart,ImuchpreferusingtheYEAR,PERIOD_DIFF,orTO_DAYSfunctions.

PostedbyStoob!onMay1220107:42pm [Delete][Edit]

IfyouhaveaVARCHARcolumncalled,forexample,`Date`thatcontainsdatedata,butisnotformattedasaMySQLDATEcolumn,youcanconvertitusingMySQL'sSTR_TO_DATEfunction.

Forinstance,Excelstorescanstoredateslikethis1/31/2010.MySQLusesthis,20100131.

Step1.CreateacolumnoftypeDATEcalled`MySQLDate`



Step2.UPDATE`table`SETMySQLDate=(SELECTSTR_TO_DATE(`Date`,'%c/%e/%Y'))

Alsohereisanothertip.ThereisaLAST_DAY()functionbutnoFIRST_DAY()function.ThishasthesameeffectofFIRST_DAY:itsubtractsamonth,findlastday,andthenadd1day.

UPDATE`table`SETstart_date=DATE_ADD(LAST_DAY(DATE_SUB(start_date,INTERVAL1MONTH)),INTERVAL1DAY)

PostedbySankarRamanathanonJuly220106:50am [Delete][Edit]

Tofindnumberofdaysinamonth.

SELECTDAY(LAST_DAY('2010021'))orSELECTDAYOFMONTH(LAST_DAY('20100201'))

Postedbynicolaslumbre

funciones de fecha y hora

Documents

format formatastimetime

time convertssecondstohh