USDTF: SOLD
O God, thank you for giving me a crazy customer like this, give him a long life and abundant fortune #GostBiker #eCash $XEC #NFTs #nftcollector pic.twitter.com/9TSUBcuQak
— Gaexe (@gaexe_) December 14, 2024
This section describes the functions that can be used to manipulate temporal values. Each temporal type has a range of valid values, as well as "null" which can be used when you want to specify an invalid value that MySQL cannot represent.
MySQL Date and Time Functions
1. NOW(fsp) and SYSDATE(fsp)
This function will return the current date and time, in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS, depending on the context in which the function is used, whether in string or numeric. The value is expressed in the current (current) time zone.
The fsp (fractional seconds precision) argument is used to determine the level of precision of the second value, so that it becomes a fraction. The level of precision is only limited from 0 to 6 behind the decimal point.
Example:
SELECT NOW();Output:
The moment when the NOW() function is executed
The moment when the NOW() function with arguments is executed
SELECT NOW(4),
SYSDATE(3);Output:
The moment when the NOW() and SYSDATE() functions are executed with arguments
2. MONTH(date) and MONTHNAME(date)
This function will return a value in the form of a month based on the arguments provided. If MONTH(date), then the value will be expressed as a number ranging from 1 to 12. If MONTHNAME(date), then the value will be expressed as text according to the name of the month.
Example:
SELECT MONTH('2017-02-21'),
MONTHNAME('2017-02-21');Output:
MONTH(date) and MONTHNAME(date)
3. DAY(date) or DAYOFMONTH(date) and DAYNAME(date)
DAY() is a synonym for DAYOFMONTH(), which is a function that will return a value in the form of a day in the range 1 to 31, or will return a value of 0 if the date argument is like this '0000-00-00' or '2008-00-00'. While DAYNAME(date) will return a value in the form of text according to the name of the day.
Example:
SELECT
DAY('2017-02-21'),
DAYOFMONTH('2017-02-21'),
DAYNAME('2017-02-21');Output:
DAY(date) or DAYOFMONTH(date) and DAYNAME(date)
4. WEEK(date, mode)
This function will return the number/order of weeks starting from the new year until the argument date. There are two types of WEEK() argument formats that allow you to specify whether the week starts on Sunday or Monday, and whether the return value should range from 0 to 53 or 1 to 53. If the mode argument is omitted, the default week format will be automatically used.
| ommand-Line Format | --default_week_format=# | |
|--------------------|-------------------------|---------------------|
| System Variable | Name | default_week_format |
| | Variable Scope | Global, Session |
| | Dynamic Variable | Yes |
| Permitted Values | Type | integer |
| | Default | 0 |
| | Min Value | 0 |
| | Max Value | 7 |The table below explains how the argument modes work;
| Mode | First day of week | Range | Week 1 is the first week … |
|------|-------------------|-------|-------------------------------|
| 0 | Sunday | 0-53 | with a Sunday in this year |
| 1 | Monday | 0-53 | with 4 or more days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with 4 or more days this year |
| 4 | Sunday | 0-53 | with 4 or more days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with 4 or more days this year |
| 7 | Monday | 1-53 | with a Monday in this year |For the mode value with the meaning of "4 or more days in the year", or in other words "the debate about the number of days in a week that fall between December and January" has been regulated according to ISO 8601:1988:
- If the week containing January 1st has 4 or more days in the new year, then it is week 1.
- Otherwise, it is the last week of the previous year, and the following week is week 1.
Meanwhile, Achmad Solichin explained that this function is used to get the week sequence (integer) from a given date in a year.
Example:
SELECT WEEK('2008-02-20'),
WEEK('2008-02-20',0),
WEEK('2008-02-20',1),
WEEK('2008-12-31',1);Output:
WEEK() function
SELECT WEEK(NOW()),
WEEK(NOW(),0),
WEEK(NOW(),1),
WEEK('2008-12-31',2);Output:
NOW() function in WEEK()
5. WEEKDAY(date)
Returns a value as the weekday index for the argument date, where 0 = Monday, 1 = Tuesday, ...to 6 = Sunday.
Example:
SELECT WEEKDAY(NOW()),
WEEKDAY('2017-2-22');Output:
WEEKDAY(date)
6. WEEKOFYEAR(date)
This function will return the week sequence of the argument date, with a range of 1 to 53. WEEKOFYEAR() is an equivalent function to WEEK(date,3).
Example:
SELECT WEEKOFYEAR(NOW());Output:
WEEKOFYEAR(date)
So it is true that NOW() is now in its 8th week. Here are the details of the review:
Manual week order calculation for now NOW() coincides with the date 22-Feb-2017
7. YEAR(date)
This function will return the year value of the argument date, ranging from 1000-9999, or 0 if it is filled with zeros.
Example:
SELECT YEAR('1987-01-01'),
YEAR(NOW());Output:
YEAR(date)
8. HOUR(time)
This function will return a time value in the form of hours. The range of values returned is from 0 to 23 time-of-day (the number of hours in a day). However, the actual time range is much larger than that (not limited by days), so the hour value can be returned greater than 23.
Meanwhile, Achmad Solichin explained that this function is used to get the hour number from a given time parameter.
Example:
SELECT HOUR(NOW()),
-- Fungsi NOW() saya running saat pukul 11.19 PM
HOUR('23:59:03'),
HOUR('272:59:59');Output:
HOUR(time)
9. MINUTE(time)
This function will return a time value in minutes, in the range 0 to 59.
Meanwhile, Achmad Solichin explained that this function is used to get the number of minutes from a given time parameter.
Example:
SELECT MINUTE('2008-02-03 10:05:03'),
MINUTE(NOW());
-- Fungsi NOW() saya running saat waktu
-- menunjukan pukul 11.25 PM.Output:
MINUTE(time)
10. SECOND(time)
This function will return a value in seconds, ranging from 0 to 59.
Meanwhile, Achmad Solichin explained that this function is used to get the number of seconds from a given time.
Example:
SELECT SECOND('10:05:03'),
SECOND(NOW());
-- Fungsi NOW() saya running saat waktu
-- menunjukan pukul 11.33.? PM.Output:
SECOND(time)
11. DATE_ADD(date,INTERVAL expr unit) and DATE_SUB(date,INTERVAL expr unit)
These two functions will perform arithmetic operations (addition/subtraction) on dates. The date argument specifies the starting date or datetime value. The expr argument is an expression to specify the range of values to be added or subtracted from the starting date. The expr argument is a string; it can start with "-" for negative ranges. The unit argument is a keyword that indicates which expression should be interpreted.
The INTERVAL keyword and the unit specifier are not case sensitive.
The following table shows what format the expr argument expects for each unit value.
| Unit Value | Expected expr Format |
|--------------------|-------------------------------------------|
| MICROSECOND | 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 | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
| DAY_HOUR | 'DAYS HOURS' |
| YEAR_MONTH | 'YEARS-MONTHS' |If you add or subtract date values that contain part-time, the result will automatically be converted to a datetime value.
Example:
SELECT DATE_ADD('2013-01-02', INTERVAL 1 DAY),
DATE_ADD('2013-01-02', INTERVAL 1 HOUR);Output:
DATE_ADD(date,INTERVAL expr units)
SELECT DATE_SUB('2013-01-02', INTERVAL 1 DAY),
DATE_SUB('2013-01-02', INTERVAL 1 HOUR);Output:
DATE_SUB(date,INTERVAL expr units)
If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has the maximum number of days for a new month, then the days will be set to the maximum in the new month.
Example:
SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH);Output:
DATE_ADD(date,INTERVAL expr units)
12. ADDDATE(date,INTERVAL expr units), ADDDATE(expr,days) and SUBDATE(date,INTERVAL expr units), SUBDATE(expr,days)
When this function is called with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). Whereas SUBDATE() is a synonym for DATE_SUB().
Example:
SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY),
ADDDATE('2008-01-02', INTERVAL 31 DAY);Output:
ADDDATE(date,INTERVAL expr units)
When the ADDDATE() function is called using the second argument format day, MySQL treats it as an integer the number of days to be added to expr.
SELECT ADDDATE('2008-01-02', 31);Output:
ADDDATE(expr,days)
SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY),
SUBDATE('2008-01-02', INTERVAL 31 DAY);Output:
SUBDATE(date,INTERVAL expr units)
When the SUBDATE() function is called using the second argument format day, MySQL treats it as an integer the number of days to be added to expr.
SELECT SUBDATE('2008-01-02 12:00:00', 31);Output:
SUBDATE(expr,days)
13. DATE_FORMAT(date,format)
This function is used to format date values according to string format.
The following specifiers can be used in format strings. The character % is required before the format specifier character.
Meanwhile, Achmad Solichin explained that this function is used to format the date display.
| Specifier | Description |
|-----------|----------------------------------------------------------------------------------------------------|
| %a | Nama hari disingkat (Sun..Sat) |
| %b | Nama bulan disingkat (Jan..Dec) |
| %c | Bulan, numerik (0..12) |
| %D | Hari dalam sebulan dengan suffix bahasa Inggris (0th, 1st, 2nd, 3rd, …) |
| %d | Hari dalam sebulan, numerik (00..31) |
| %e | Hari dalam sebulan, numerik (0..31) |
| %f | Mikrodetik (000000..999999) |
| %H | Jam (00..23) |
| %h | Jam (01..12) |
| %I | Jam (01..12) |
| %i | Menit, numerik (00..59) |
| %j | Hari dalam setahun (001..366) |
| %k | Jam (0..23) |
| %l | Jam (1..12) |
| %M | Nama bulan (January..December) |
| %m | Bulan, numerik (00..12) |
| %p | AM atau PM |
| %r | Waktu, 12-jam (hh:mm:ss followed by AM or PM) |
| %S | Detik (00..59) |
| %s | Detik (00..59) |
| %T | Waktu, 14-jam (hh:mm:ss) |
| %U | Seminggu (00..53), dimana hari Minggu sebagai hari pertamanya; WEEK() mode 0 |
| %u | Seminggu (00..53), dimana hari Senin sebagai hari pertamanya; WEEK() mode 1 |
| %V | Seminggu (01..53), dimana hari Minggu sebagai hari pertamanya; WEEK() mode 2, digunakan dengan %X |
| %v | Seminggu (01..53), dimana hari Senin sebagai hari pertamanya; WEEK() mode 3, digunakan dengan %X |
| %W | Nama hari dalam seminggu (Sunday..Saturday) |
| %w | Hari dalam seminggu (0=Sunday..6=Saturday) |
| %X | Tahun untuk mingguan, dimana Minggu sebagai hari pertamanya, numerik, 4 digit, digunakan dengan %V |
| %x | Tahun untuk mingguan, dimana Senin sebagai hari pertamanya, numerik, 4 digit, digunakan dengan %V |
| %Y | Tahun, numerik, 4 digit |
| %y | Tahun, numerik, 2 digit |
| %% | Literal karakter % |
| %x | X, untuk setiap "X" yang tidak tercantum dalam daftar diatas. |The range for the month and day specifiers starts at zero due to the fact that MySQL allows storing incomplete dates such as '2014-00-00'.
The language used for naming days and months is abbreviated, this is controlled by the value of the system variable Ic_time_names.
For the %U, %u, %V, and %v specifiers, you can see them in the explanation of the WEEK() function. The use of mode will affect the week numbering.
DATE_FORMAT() returns a string with the character set and collation performed by character_set_connection and collation_connection, so it can return month and day names that contain non-ASCII characters.
Example:
SELECT DATE_FORMAT (now(), '%d-%M-%Y %H:%i:%s') AS Contoh1,
DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') AS Contoh2,
DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') AS Contoh3,
DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') AS Contoh4,
DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') AS Contoh5,
DATE_FORMAT('1999-01-01', '%X %V') AS Contoh6,
DATE_FORMAT('2006-06-00', '%d') AS Contoh7;Output:
DATE_FORMAT(date,format)
Reference:
- https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
- STMIK EL RAHMA Database System Practical Module, By Wahyu Widodo
- MySQL5 Book From Beginner to Advanced by Achmad Solichin