Date syntax in mysql

Date syntax in mysql DEFAULT

SQL Date Functions


SQL Dates

NoteThe most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.


MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

FunctionDescription
NOW()Returns the current date and time
CURDATE()Returns the current date
CURTIME()Returns the current time
DATE()Extracts the date part of a date or date/time expression
EXTRACT()Returns a single part of a date/time
DATE_ADD()Adds a specified time interval to a date
DATE_SUB()Subtracts a specified time interval from a date
DATEDIFF()Returns the number of days between two dates
DATE_FORMAT()Displays date/time data in different formats

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:

FunctionDescription
GETDATE()Returns the current date and time
DATEPART()Returns a single part of a date/time
DATEADD()Adds or subtracts a specified time interval from a date
DATEDIFF()Returns the time between two dates
CONVERT()Displays date/time data in different formats

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

  • DATE - format YYYY-MM-DD
  • DATETIME - format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
  • YEAR - format YYYY or YY

SQL Server comes with the following data types for storing a date or a date/time value in the database:

  • DATE - format YYYY-MM-DD
  • DATETIME - format: YYYY-MM-DD HH:MI:SS
  • SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP - format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

For an overview of all data types available, go to our complete Data Types reference.


SQL Working with Dates

NoteYou can compare two dates easily if there is no time component involved!

Assume we have the following "Orders" table:

OrderIdProductNameOrderDate
1Geitost2008-11-11
2Camembert Pierrot2008-11-09
3Mozzarella di Giovanni2008-11-11
4Mascarpone Fabioli2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderIdProductNameOrderDate
1Geitost2008-11-11
3Mozzarella di Giovanni2008-11-11

Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate" column):

OrderIdProductNameOrderDate
1Geitost2008-11-11 13:23:44
2Camembert Pierrot2008-11-09 15:45:21
3Mozzarella di Giovanni2008-11-11 11:12:01
4Mascarpone Fabioli2008-10-29 14:56:59

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.

Tip: If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!


Sours: http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_dates.asp.html

MySQL Date Functions

MySQL-Date-Functions

Introduction to MySQL Date Functions

The MySQL Date function is responsible for extracting the data section from the specified date or, expression DateTime. In MySQL, DATE() functions are used to store DateTime values and help to return the current date and time both parts when retrieved and displayed in the MySQL Server format ‘YYYY-MM-DD HH:MM: SS’. There are several significant MySQL Date Functions but it may be a bit complicated with time functions. Some of them are used to get the date, add a date, view format of the date, even separate date and time, getting the number of days existing between a couple of dates and also performing some sum or subtract operations for the provided specified time interval in a date function.

Importance of MySQL Date Functions

Let us study some of the important DATE functions used in MySQL and applicable for different scopes in the maintenance of databases and respective tables:

  • ADDDATE(): This MySQL DATE function supplements a time value along with the date value.
  • CURDATE(): This function provides the current date where the result is defined in two different formats either ‘YYYY-MM-DD’or ‘YYYYMMDD’ that depends on the argument to be a string or numeric given in the function.
  • CURRENT_DATE(): It also works identical to the CURDATE() function and returns the present date in the respective two formats mentioned above.
  • DATE_ADD(): This function adds the value of time in the form of intervals to a date. The ADDDATE() function is a substitute for this function.
  • DATE_FORMAT(): This MySQL Date function organizes a date as indicated in the argument of the function. Some formats are ‘YYYY-MM-DD’, ‘YYYYMMDD’, etc.
  • DATE_SUB(): This MySQL Date function is responsible to subtract a time interval value from a date.
  • DATE(): This MySQL Date function allow02s to provide the date portion out from a given DATETIME() value expression.
  • DATEDIFF(): This MySQL Date function returns the result as the number of days found between any two specified datetimes or dates.
  • EXTRACT(): This function extracts a section of a date expression.
  • LOCALTIME(): This MySQL Date function provides the current date and time values in a special format i.e. ‘YYYY-MM-DD HH:MM: SS’ or ‘YYYYMMDDHHMMSS which depends on the type of arguments passed in the context as a string or numeric’ of the function.
  • MAKEDATE(): This type of Date function is used to return date by captivating a value of a year and a number of days where the day value should be greater than 0. If this condition is not provided then, the function will return a NULL value.
  • MONTH(): This type of function returns the value of the month for the specified date which should be between range from 1 to 12 i.e. Jan to Dec.
  • NOW(): This MySQL Date function gives the value of current date and time in a distinct format i.e. ‘YYYY-MM-DD HH:MM:SS’ or ‘YYYYMMDDHHMMSS which depends on the type of arguments passed in the context as a string or numeric’ of the function.
  • STR_TO_DATE(): It provides the value of datetime with the help of a string and its indicated format passed in the arguments of the function.
  • SUBDATE(): It is responsible for providing a time period value from a particular date.
  • SYSDATE(): This function offers the value of current date and time in a separate format i.e. ‘YYYY-MM-DD HH:MM: SS’ or ‘YYYYMMDDHHMMSS which is determined by the type of arguments passed in the context as a string or numeric’ of the function.
  • UTC_DATE(): On the basis of the arguments as a string or numeric in the function, the result i.e. current Coordinated Universal Time (UTC) date value is delivered in the layout like ‘YYYY-MM-DD’ or ‘YYYYMMDD’.
  • GETDATE(): It returns the present date with the time.
  • CONVERT(): This function helps to display the result of date and time in diverse formats.

MySQL for DATE and TIME functions

Let us view some of the Data Types formats used in MySQL for DATE and TIME functions and storing the values:

  • DATE: YYYY-MM-DD
  • DATETIME: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP: YYYY-MM-DD HH:MM:SS
  • YEAR: YYYY or YY

The syntax for Date function can be defined as follows:

  • ‘Expr’ denotes the required and valid expression that is the date or datetime value. If not so then the date function returns a NULL value.
  • Supposing we want to show the current date then, we use the below query:

Code:

Output:

current

Code:

Output:

select

Syntax: Using DATE_ADD() and its syntax is mentioned as follows:

Here,date-value is the date to be altered, INTERVAL_Value defines the value of date and time interval that can be both positive or negative and interval_unitis the type of date interval to be indicated in the function.

Code:

Output:

interval

  • The above date function in MySQL when executed returns the date value where 10 mins is added to the mentioned date in the statement.
  • Now, we will write a query where we will subtract 2 hours from a given date and time:

Code:

Output:

MySQL Date Functions - 4

Using DATE_FORMAT() date function, we create the following query with the given syntax:

SQL Statement:

Output:

MySQL Date Functions - 5

Using DATE_SUB() date function, we will use the subsequent MySQL query with the specified syntax:

SQL Statement:

Output:

MySQL Date Functions - 6

This function returns the date after subtracting 5 days from the given date. Let us return the difference of dates by using the date function DATEDIFF where two expressions are provided:

Code:

Output:

MySQL Date Functions - 7

Conclusion

The function provides us many benefits to store the date and time of any records inserted, deleted, altered or accessed as well as to keep the information of Restores, Transaction process, Rollback, Backup, business records like orders, and sales and login logout sessions and other different MySQL operations.

We use this MySQL Date functions to make the records updated, well-managed, and related with the date and time factors which is helpful to find out any query in the Server.

Recommended Articles

This is a guide to MySQL Date Functions. Here we discuss an introduction, important, and examples to implement. You can also go through our other related articles to learn more –

  1. MySQL IN Operator
  2. ANY in MySQL
  3. MySQL MAX() Function
  4. MySQL Constraints
Sours: https://www.educba.com/mysql-date-functions/
  1. Gta 5 stocks
  2. Paw patrol robot episode
  3. Used boat motor

SQL Working With Dates

❮ PreviousNext ❯


SQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated.


SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

  • - format YYYY-MM-DD
  • - format: YYYY-MM-DD HH:MI:SS
  • - format: YYYY-MM-DD HH:MI:SS
  • - format YYYY or YY

SQL Server comes with the following data types for storing a date or a date/time value in the database:

  • - format YYYY-MM-DD
  • - format: YYYY-MM-DD HH:MI:SS
  • - format: YYYY-MM-DD HH:MI:SS
  • - format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!


SQL Working with Dates

Look at the following table:

Orders Table

OrderIdProductNameOrderDate
1Geitost2008-11-11
2Camembert Pierrot2008-11-09
3Mozzarella di Giovanni2008-11-11
4Mascarpone Fabioli2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderIdProductNameOrderDate
1Geitost2008-11-11
3Mozzarella di Giovanni2008-11-11

Note: Two dates can easily be compared if there is no time component involved!

Now, assume that the "Orders" table looks like this (notice the added time-component in the "OrderDate" column):

OrderIdProductNameOrderDate
1Geitost2008-11-11 13:23:44
2Camembert Pierrot2008-11-09 15:45:21
3Mozzarella di Giovanni2008-11-11 11:12:01
4Mascarpone Fabioli2008-10-29 14:56:59

If we use the same statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.

Tip: To keep your queries simple and easy to maintain, do not use time-components in your dates, unless you have to!


❮ PreviousNext ❯


Sours: https://www.w3schools.com/sql/sql_dates.asp

11.2.2 The DATE, DATETIME, and TIMESTAMP Types

The , , and types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes , , and values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the and range descriptions, “supported” means that although earlier values might work, there is no guarantee.

The type is used for values with a date part but no time part. MySQL retrieves and displays values in format. The supported range is to .

The type is used for values that contain both date and time parts. MySQL retrieves and displays values in format. The supported range is to .

The data type is used for values that contain both date and time parts. has a range of UTC to UTC.

A or value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a or column is stored rather than discarded. With the fractional part included, the format for these values is , the range for values is to , and the range for values is to . The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.6, “Fractional Seconds in Time Values”.

The and data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

MySQL converts values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as .) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the system variable. For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.

In MySQL 8.0.19 and later, you can specify a time zone offset when inserting a or value into a table. See Section 9.1.3, “Date and Time Literals”, for more information and examples.

Invalid , , or values are converted to the “zero” value of the appropriate type ( or ), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.

In MySQL 8.0.22 and later, you can convert values to UTC values when retrieving them using with the operator, as shown here:

For complete information regarding syntax and additional examples, see the description of the function.

Be aware of certain properties of date value interpretation in MySQL:

  • MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as might look like a time value because of the , but is interpreted as the year if used in date context. The value is converted to because is not a valid month.

    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

  • The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as are converted to and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable . See Section 5.1.11, “Server SQL Modes”, for more information.

  • MySQL does not accept values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value , if the SQL mode permits this value. The precise behavior depends on which if any of strict SQL mode and the SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.

  • Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:

    • Year values in the range become .

    • Year values in the range become .

    See also Section 11.2.8, “2-Digit Years in Dates”.


Sours: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Mysql date syntax in

MySQL and datetime

As stated in Date and Time Literals:

MySQL recognizes values in these formats:

  • As a string in either or format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, , , , and are equivalent.

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has nonsensical month and day parts) and becomes .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

MySQL recognizes and values in these formats:

  • As a string in either or format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, , , , and are equivalent.

    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

    The date and time parts can be separated by rather than a space. For example, are equivalent.

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has a nonsensical minute part) and becomes .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

Notably, MySQL does not support the incomplete formats that you wish to use.

That MySQL happens to accept some of the incomplete formats you've tried (apparently by padding with zeroes) is undocumented behaviour, quite possibly unintended by the developers. It cannot (and should not) be relied upon, not least because edge cases could exist under which the behaviour breaks; or because the behaviour could be changed without warning in a future release.

If it's absolutely necessary to provide such incomplete temporal literals to MySQL (which it shouldn't be, as your data access layer ought to be aware of the type of values it is handling and provide them to MySQL in a supported format), you can use its function to parse them accordingly:

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

Range checking on the parts of date values is as described in Section 11.3.1, “The DATE, DATETIME, and TIMESTAMP Types”. This means, for example, that “zero” dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

So, for example, you might use:

answered Mar 25 '15 at 11:25

eggyaleggyal

115k1818 gold badges190190 silver badges224224 bronze badges

Sours: https://stackoverflow.com/questions/29253679/mysql-and-datetime
MySQL: DATE() Function Syntax \u0026 Examples

9.1.3 Date and Time Literals

Date and time values can be represented in several formats, such as quoted strings or as numbers, depending on the exact type of the value and other factors. For example, in contexts where MySQL expects a date, it interprets any of , , and as a date.

This section describes the acceptable formats for date and time literals. For more information about the temporal data types, such as the range of permitted values, see Section 11.2, “Date and Time Data Types”.

Standard SQL and ODBC Date and Time Literals.  Standard SQL requires temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.

MySQL recognizes but, unlike standard SQL, does not require the type keyword. Applications that are to be standard-compliant should include the type keyword for temporal literals.

MySQL also recognizes the ODBC syntax corresponding to the standard SQL syntax:

MySQL uses the type keywords and the ODBC constructions to produce , , and values, respectively, including a trailing fractional seconds part if specified. The syntax produces a value in MySQL because has a range that more closely corresponds to the standard SQL type, which has a year range from to . (The MySQL year range is to .)

String and Numeric Literals in Date and Time Context.  MySQL recognizes values in these formats:

  • As a string in either or format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, , , , and are equivalent.

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has nonsensical month and day parts) and becomes .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

MySQL recognizes and values in these formats:

  • As a string in either or format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, , , , and are equivalent.

    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

    The date and time parts can be separated by rather than a space. For example, are equivalent.

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has a nonsensical minute part) and becomes .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

A or value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.6, “Fractional Seconds in Time Values”.

Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:

  • Year values in the range become .

  • Year values in the range become .

See also Section 11.2.8, “2-Digit Years in Dates”.

For values specified as strings that include date part delimiters, it is unnecessary to specify two digits for month or day values that are less than . is the same as . Similarly, for values specified as strings that include time part delimiters, it is unnecessary to specify two digits for hour, minute, or second values that are less than . is the same as .

Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in or format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in or format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as nondelimited strings are interpreted according their length. For a string 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify , thinking that represents March, 1999, MySQL converts it to the “zero” date value. This occurs because the year and month values are and , but the day part is completely missing. However, you can explicitly specify a value of zero to represent missing month or day parts. For example, to insert the value , use .

MySQL recognizes values in these formats:

  • As a string in format. You can also use one of the following “relaxed” syntaxes: , , , , or . Here represents days and can have a value from 0 to 34.

  • As a string with no delimiters in format, provided that it makes sense as a time. For example, is understood as , but is illegal (it has a nonsensical minute part) and becomes .

  • As a number in format, provided that it makes sense as a time. For example, is understood as . The following alternative formats are also understood: , , or .

A trailing fractional seconds part is recognized in the , , , and time formats, where is the fractional part in up to microseconds (6 digits) precision. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.6, “Fractional Seconds in Time Values”.

For values specified as strings that include a time part delimiter, it is unnecessary to specify two digits for hours, minutes, or seconds values that are less than . is the same as .

Beginning with MySQL 8.0.19, you can specify a time zone offset when inserting and values into a table. The offset is appended to the time part of a datetime literal, with no intravening spaces, and uses the same format used for setting the system variable, with the following exceptions:

  • For hour values less than than 10, a leading zero is required.

  • The value is rejected.

  • Time zone names such as and cannot be used; also cannot be used in this context.

The value inserted must not have a zero for the month part, the day part, or both parts. This is enforced beginning with MySQL 8.0.22, regardless of the server SQL mode setting.

This example illustrates inserting datetime values with time zone offsets into and columns using different settings, and then retrieving them:

The offset is not displayed when selecting a datetime value, even if one was used when inserting it.

The range of supported offset values is to , inclusive.

Datetime literals that include time zone offsets are accepted as parameter values by prepared statements.


Sours: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

Now discussing:

Introduction

MySQL comes with many built-in functions that allow you to manipulate data. These functions are grouped into categories – date functions, string functions, mathematic functions, and others.

Date functions give you numerous options on how to modify, calculate, and convert date, time, and datetime expressions in MySQL.

In this tutorial, you will learn about MySQL date and time functions and how they work, on practical examples.

MySQL date functions guide with examples

Date-Related Functions

CURDATE OR CURRENT_DATE

Return the current date in the “YYY-MM-DD” or “YYYYMMDD” format with the OR command.

The basic syntax:

For example, if you run:

MySQL responds with the current date in the format:

DATE

Return the date from a datetime expression using the command.

The basic syntax:

For instance, if you run:

The output is:

DATE_ADD or ADDDATE

Add a time/date value to a date expression with the or function.

The basic syntax:

Replace date with the date expression you want to add a time/date to. The value unit is the time/date you want to add. It needs to be expressed in a numeric value along with the unit of the value.

The unit can be:

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

For example, if you run:

The output returns the result:

DATE_FORMAT

Format a date using .

The basic syntax:

The date is the date expression you want to reformat, while the formatis a combination of the following specifiers:

Specifiers used to create date formats for MySQL DATE_FORMAT function.

For instance, if you run:

The output displays the result:

DATE_SUB or SUBDATE

Subtract a time/date value to a date expression with the or function.

The basic syntax:

Replace date with the date expression you want to subtract from. The value unit is the time/date you want to subtract. It needs to be expressed in numeric value along with the unit of the value.

Find a list of unit types in the DATE_ADD section.

For instance, if you run:

The output returns the result:

DATEDIFF

Return the number of days between two date expressions with the function.

The basic syntax:

For example:

Returns the result:

EXTRACT

To extract part of a date/datetime expression, use the function.

The basic syntax:

In the command, you need to specify which unit you want to extract from the specified date.

Find a list of units you can use in the DATE_ADD description.

For instance, when you run:

You get the result:

GET_FORMAT

Return a format string (a combination of specifiers) as specified in the argument with . This function is often used with .

The basic syntax:

Use this function with date, time, and datetime expressions.

The format can be:

  • ‘EUR’
  • ‘USA’
  • ’JIS’
  • ’ISO’
  • ‘INTERNAL’

There is a finite number of results you can get using the function. Below you will find a list of all the function calls and their results.

MySQL GET_FORMAT function calls and their results.

For instance, you could combine the function with , as in the following example:

Where the result is:

MAKEDATE

Return a date expression from a specified year and day of year using the function.

The basic syntax:

For example, if you run:

The output shows the result:

STR_TO_DATE

Format a date from a string with and return a date/datetime value.

The basic syntax:

The string is what you want to reformat, while the format is a combination of specifiers that describe each element of the string.

You can find a list of specifiers and their meaning in the DATE_FORMAT section.

For instance, if you run:

The output shows:

SYSDATE

To return the current date and time in the format “YYYY-MM-DD hh:mm:ss” or “YYYYMMDDHHMMSS.uuuuuu”, use the function.

The basic syntax:

You can add the argument to include fractional second precision (0-6). In that case, the syntax is .

The command shown below:

At this time gives the result:

UTC_DATE

Return the current Coordinated Universal Time (UTC) date value in the “YYYY-MM-DD” or “YYYYMMDD” format with the function.

The basic syntax:

For example, running the following command:

Returns the current date which is:

Time-Related Functions

ADDTIME

Add a time interval to a specified time/datetime expression using .

The basic syntax:

For instance, if you run:

You get the result:

CONVERT_TZ

Convert a time/datetime expression from one time zone to another using the function.

The basic syntax:

For example, when you run:

MySQL converts the specified datetime to +10:00 time zone:

CURTIME or CURRENT_TIME

Return the current time using the or function. The result returns the time in the “hh:mm:ss” or “hhmmss” format.

The basic syntax:

You can include fractional second precision (from 0 to 6) by adding the argument.

For example, the following command shows the current time with three fractional second precision:

The output responds with the result:

HOUR

Return the hour of the specified time/datetime with the function.

The basic syntax:

For instance, if you run:

The result is:

MAKETIME

Return a time expression from the specified hour, minute, and second values using the function.

The basic syntax:

For example, you can run:

Where the output displays:

MICROSECOND

Return the microseconds of the specified time/datetime expression with .

The basic syntax:

For example, you can run:

Where the result is:

MINUTE

Return the minutes of the specified time/datetime expression using the function.

The basic syntax:

For instance, if you run the command:

The result is:

SEC_TO_TIME

Return a time value from a specified seconds value with the function.

The basic syntax:

For instance, if you run the command:

The output is:

SUBTIME

Subtract a time value from a time/datetime expression using the function.

The basic syntax:

For example, when you run:

The output is:

TIME

To return the time value from a datetime expression, use the function.

The basic syntax:

For instance:

Gives the result:

TIME_FORMAT

Format a time value into the specified format with .

The basic syntax:

The format is a combination of specifiers. You can find a list of all specifiers and their meaning in the description of the DATE_FORMAT function.

For example, by running:

You get the output:

TIME_TO_SEC

To return the time value converted into seconds use the .

The basic syntax:

For instance, when you run:

The result is:

TIMEDIFF

Calculate the difference between two time/datetime expressions with the function. In this case, the result is always in time value.

The basic syntax:

For instance, when you run:

The output returns:

TO_SECONDS

To convert a date/datetime expression into seconds, use the function . The result is the number of seconds between 0 and the specified date/datetime.

The basic syntax:

For example, if you run the command:

The result is:

UTC_TIME

Return the current UTC time value with . It returns the time value in the “HH:MM:SS” or “HHMMSS” format.

The basic syntax:

For example, if you run:

You get the result at this point of time is:

Note: Do you know how a traditional relational database compares to a document-oriented database management system? Learn more in this analysis of MySQL Vs. MongoDB.

Timestamp-Related Functions

CURRENT_TIMESTAMP or LOCALTIMESTAMP

To return the current date and time, use or . The result returns in the “YYYY-MM-DD HH-MM-SS” or “YYYYMMDDHHMMSS.uuuuuu” format.

The basic syntax:

For example, by running:

The current result is:

FROM_UNIXTIME

Return a date/datetime expression from a timestamp in the Unix format with .

The basic syntax:

If you run the command without an argument specifying the format, it returns the result in the “YYYY-MM-DD hh:mm:ss” or “YYYYMMDDhhmmss” format.

For instance, if you run:

You get the result:

TIMESTAMP

To return a datetime expression from a date or datetime value, use the function . If you add two arguments, the output returns the sum of the arguments.

The basic syntax:

For example, when you run the command:

The output shows the result:

TIMESTAMPADD

Add a time value to a date/datetime expression using the function.

The basic syntax:

The unit can be:

  • FRAC_SECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

For instance, the following command adds 3 days to the specified date:

Therefore, the output shows:

TIMESTAMPDIFF

To calculate the difference between two date/datetime expressions, use . The function subtracts one datetime value from the other in the specified unit.

The basic syntax:

You can find a list with different types of units, check out the list in the section above.

For example, you can calculate the difference between the following two dates in days:

Where the result is:

UNIX_TIMESTAMP

Return a Unix timestamp from a date/datetime expression with the function. The Unix timestamp represents seconds between the specified datetime and “1970-01-01 00:00:00” UTC.

The basic syntax:

For instance, running the following command:

Gives the result:

UTC_TIMESTAMP

Return the current UTC date and time value with . It returns the datetime value in the “YYYY-MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS.uuuuuu” format.

The basic syntax:

For example, the command:

Returns output in the same format as the one below:

Day/Week/Month/Year-Related Functions

DAY

Return the day of a month from a specified date/datetime expression with the function.

The basic syntax:

If you run the command below:

The output returns the result:

DAYNAME

Return the name of the weekday from a specified date/datetime expression using the function.

The basic syntax:

For example, when you run the command:

MySQL responds with the result:

DAYOFMONTH

Return the day of a month from a specified date/datetime expression with .

The basic syntax:

For example, when you run the command:

MySQL responds with the result:

DAYOFWEEK

Return the day of week in numerical value from the specified date/datetime expression using .

The basic syntax:

Running the command below:

Gives the response:

DAYOFYEAR

Return the day in a year from the specified date/datetime expression using the function .

The basic syntax:

For instance, when you run the command:

The output gives the result:

FROM_DAYS

Return a date expression from a numeric representation of a day using the function .

The basic syntax:

For example, running:

The MySQL prompt responds with the result:

LAST_DAY

Return the last day of the month from a specified date/datetime with the function.

The basic syntax:

For instance, if you run the following command:

The output responds with:

MONTH

Return the month (in numeric value) from a specified date/datetime by using the function.

The basic syntax:

For example, when you run:

MySQL responds with:

MONTHNAME

Return the name of the month from a specified date/datetime with the function.

The basic syntax:

If you run the command:

You get the following response:

PERIOD_ADD

To add a specified number of months to a period, use the function.

The basic syntax:

The periodis defined in the format YYMM or YYYYMM, while the number is the number of months you want to add.

For example:

Gives the result:

PERIOD_DIFF

Return the number of months between two periods with .

The basic syntax:

Each period should be in the format YYMM or YYYYMM.

For instance:

Produces the result:

QUARTER

To return a quarter of a year from a specified date/datetime, use the function .

The basic syntax:

For example, by running the command:

The output responds with:

TO_DAYS

Convert a date/datetime expression to a numeric representation of a day with .

The basic syntax:

For instance, for the command:

The result is :

WEEK

To return the week number from a specified date, use the function .

The basic syntax:

You can also include the mode argument, in which case the syntax is .

The mode argument specifies from which day the week starts. If there is no argument, it uses mode by default.

The following table describes each mode:

MySQL mode argument for time functions.

For example, if you run:

The output responds with:

WEEKDAY

Return the weekday from the specified date in numeric value with . Each number represents one of the weekdays – Monday is , Tuesday is , and so on.

The basic syntax:

For example, running the following command:

Gives the response:

WEEKOFYEAR

To return the number of a week in a year, use the function.

The basic syntax:

For instance, when you run the command:

The output shows the result:

YEAR

Return the year from the specified date with the function.

The basic syntax:

If you run the following command:

You get the result:

YEARWEEK

Return the year and week number from the specified date using the function.

The basic syntax:

You can add a argument to the basic syntax to specify the day from which the week begins. To see a list of modes and their meaning, check out the table in the function.

For example, when you run the command:

The output displays:

Conclusion

This article should help you find all the MySQL date (and time) functions. With descriptions for each function and practical examples, you should be able to apply them with ease.

To have a better understanding of different data types, we suggest to check out our article on MySQL Data Types.

Was this article helpful?

YesNo

Sours: https://phoenixnap.com/kb/mysql-date-function


1307 1308 1309 1310 1311