Erinevus lehekülje "Kuupäevafunktsioonid" redaktsioonide vahel
Allikas: Teadmusbaas
| 32. rida: | 32. rida: | ||
<tr><td>DAY_HOUR </td><td>päevad tunnid</td></tr> | <tr><td>DAY_HOUR </td><td>päevad tunnid</td></tr> | ||
<tr><td>YEAR_MONTH </td><td>aastad-kuud</td></tr> | <tr><td>YEAR_MONTH </td><td>aastad-kuud</td></tr> | ||
| + | </table> | ||
| + | |||
| + | |||
| + | <table border=1> | ||
| + | <tr><th>Võti</th><th>Kirjeldus</th></tr> | ||
| + | <tr><td>%a</td><td>Short weekday name in current locale (Variable lc_time_names)</td></tr> | ||
| + | <tr><td>%b</td><td>Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.</td></tr> | ||
| + | <tr><td>%c </td><td>Month with 1 or 2 digits.</td></tr> | ||
| + | <tr><td>%D </td><td>Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).</td></tr> | ||
| + | <tr><td>%d </td><td>Day with 2 digits.</td></tr> | ||
| + | <tr><td>%e </td><td>Day with 1 or 2 digits.</td></tr> | ||
| + | <tr><td>%f </td><td>Sub seconds 6 digits.</td></tr> | ||
| + | <tr><td>%H </td><td>Hour with 2 digits between 00-23.</td></tr> | ||
| + | <tr><td>%h </td><td>Hour with 2 digits between 01-12.</td></tr> | ||
| + | <tr><td>%I </td><td>Hour with 2 digits between 01-12.</td></tr> | ||
| + | <tr><td>%i </td><td>Minute with 2 digits.</td></tr> | ||
| + | <tr><td>%j </td><td>Day of the year (001-366)</td></tr> | ||
| + | <tr><td>%k </td><td>Hour with 1 digits between 0-23.</td></tr> | ||
| + | <tr><td>%l </td><td>Hour with 1 digits between 1-12.</td></tr> | ||
| + | <tr><td>%M </td><td>Full month name in current locale (Variable lc_time_names).</td></tr> | ||
| + | <tr><td>%m </td><td>Month with 2 digits.</td></tr> | ||
| + | <tr><td>%p </td><td>AM/PM according to current locale (Variable lc_time_names).</td></tr> | ||
| + | <tr><td>%r </td><td>Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.</td></tr> | ||
| + | <tr><td>%S </td><td>Seconds with 2 digits.</td></tr> | ||
| + | <tr><td>%s </td><td>Seconds with 2 digits.</td></tr> | ||
| + | <tr><td>%T </td><td>Time in 24 hour format. Short for '%H:%i:%S'.</td></tr> | ||
| + | <tr><td>%U </td><td>Week number (00-53), when first day of the week is Sunday.</td></tr> | ||
| + | <tr><td>%u </td><td>Week number (00-53), when first day of the week is Monday.</td></tr> | ||
| + | <tr><td>%V </td><td>Week number (01-53), when first day of the week is Sunday. Used with %X.</td></tr> | ||
| + | <tr><td>%v </td><td>Week number (01-53), when first day of the week is Monday. Used with %x.</td></tr> | ||
| + | <tr><td>%W </td><td>Full weekday name in current locale (Variable lc_time_names).</td></tr> | ||
| + | <tr><td>%w </td><td>Day of the week. 0 = Sunday, 6 = Saturday.</td></tr> | ||
| + | <tr><td>%X </td><td>Year with 4 digits when first day of the week is Sunday. Used with %V.</td></tr> | ||
| + | <tr><td>%x </td><td>Year with 4 digits when first day of the week is Monday. Used with %v.</td></tr> | ||
| + | <tr><td>%Y </td><td>Year with 4 digits.</td></tr> | ||
| + | <tr><td>%y </td><td>Year with 2 digits.</td></tr> | ||
| + | <tr><td>%# </td><td>For str_to_date(), skip all numbers.</td></tr> | ||
| + | <tr><td>%. </td><td>For str_to_date(), skip all punctation characters.</td></tr> | ||
| + | <tr><td>%@ </td><td>For str_to_date(), skip all alpha characters.</td></tr> | ||
| + | <tr><td>%% </td><td>A literal % character.</td></tr> | ||
</table> | </table> | ||
Redaktsioon: 13. mai 2019, kell 10:20
MariaDb kuupäeva ja kellaaja funtsioonid on ADDDATE, ADDTIME, CURDATE, DATEDIFF, DAYNAME, EXTRACT, LAST_DAY, NOW, SYSDATE jpt, täielik loetelu vt allikas 1
- Kuupäevasid saab liita ja lahutada funktsioonidega ADDDATE, DATE_ADD, SUBDATE ning kellaaegasid liita ja lahutada funktsioonidega ADDTIME ja SUBTIME, jooksvat kuupäeva saab kuvada funktsioonidega CURDATE, NOW ja SYSDATE, jooksvat kellaaega saab vaadata CURTIME funktsiooniga;
- ADDDATE funktsioonil on 2 kuju: ADDDATE(kuupäev,INTERVAL väärtus ja ühik vt tabel 1) või ADDDATE(kuupäeva_avaldis,päevade arv), näiteks
SELECT ADDDATE('2018-01-02', INTERVAL 31 DAY); - Jooksvat kuupäeva saab funktsioonidega NOW() ja SYSDATE: SYSDATE([täpsus]), näiteks
SELECT SYSDATE(4)annab kellaaja sekundid 4 komakohaga; - Kahe kuupäeva vahelist päevade arvu arvutab DATEDIFF, millel on 2 argumenti hilisem kuupäev ja varasem kuupäev, näiteks
SELECT DATEDIFF(NOW(),"2007-04-21");; - EXTRACT funktsioon võimaldab kuupäevast tuletada vajaliku kuupäeva osa (päeva, kuu, aasta), näiteks
SELECT EXTRACT(YEAR FROM '2019-07-02');tuletab aastaarvu; - LAST_DAY(kuupäev) väljastab kuupäeva järgi kuu viimase päeva;
- MONTHNAME(kuupäev)väljastab kuupäevaga määratud kuu nime;
- DATE_FORMAT(date, format[, locale]) ja TIME_FORMAT vormindavad kuupäeva või kellaaega etteantud parameetrite järgi, vt tabel 2.
Tabel 1. funktsioonides ADDDATE, SUBDATE, DATE_ADD, DATE_SUB, TIMESTAMPADD, TIMESTAMPDIFF, EXTRACT kasutatavad ühikud
| Ühik | Kirjeldus |
|---|---|
| MICROSECOND | mikrosekundid |
| SECOND | sekundid |
| MINUTE | minutid |
| HOUR | tunnid |
| DAY | päevad |
| WEEK | nädalad |
| MONTH | kuud |
| QUARTER | kvartalid |
| YEAR | aastad |
| SECOND_MICROSECOND | sekundid.mikrosekundid |
| MINUTE_MICROSECOND | minutid.sekundid.mikrosekundid |
| MINUTE_SECOND | minutid.sekundid |
| HOUR_MICROSECOND | tunnid.minutid.sekundid.mikrosekundid |
| HOUR_SECOND | tunnid.minutid.sekundid |
| HOUR_MINUTE | tunnid.minutid |
| DAY_MICROSECOND | päevad tunnid.minutid.sekundid.mikrosekundid |
| DAY_SECOND | päevad tunnid.minutid.sekundid |
| DAY_MINUTE | päevad tunnid.minutid |
| DAY_HOUR | päevad tunnid |
| YEAR_MONTH | aastad-kuud |
| Võti | Kirjeldus |
|---|---|
| %a | Short weekday name in current locale (Variable lc_time_names) |
| %b | Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec. |
| %c | Month with 1 or 2 digits. |
| %D | Day with English suffix 'th', 'nd', 'st' or 'rd. (1st, 2nd, 3rd...). |
| %d | Day with 2 digits. |
| %e | Day with 1 or 2 digits. |
| %f | Sub seconds 6 digits. |
| %H | Hour with 2 digits between 00-23. |
| %h | Hour with 2 digits between 01-12. |
| %I | Hour with 2 digits between 01-12. |
| %i | Minute with 2 digits. |
| %j | Day of the year (001-366) |
| %k | Hour with 1 digits between 0-23. |
| %l | Hour with 1 digits between 1-12. |
| %M | Full month name in current locale (Variable lc_time_names). |
| %m | Month with 2 digits. |
| %p | AM/PM according to current locale (Variable lc_time_names). |
| %r | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
| %S | Seconds with 2 digits. |
| %s | Seconds with 2 digits. |
| %T | Time in 24 hour format. Short for '%H:%i:%S'. |
| %U | Week number (00-53), when first day of the week is Sunday. |
| %u | Week number (00-53), when first day of the week is Monday. |
| %V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
| %v | Week number (01-53), when first day of the week is Monday. Used with %x. |
| %W | Full weekday name in current locale (Variable lc_time_names). |
| %w | Day of the week. 0 = Sunday, 6 = Saturday. |
| %X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
| %x | Year with 4 digits when first day of the week is Monday. Used with %v. |
| %Y | Year with 4 digits. |
| %y | Year with 2 digits. |
| %# | For str_to_date(), skip all numbers. |
| %. | For str_to_date(), skip all punctation characters. |
| %@ | For str_to_date(), skip all alpha characters. |
| %% | A literal % character. |
