In MySQL, the TIMESTAMP() function returns a datetime value based on the argument/s passed in. You can provide one argument or two. If you provide two, it adds the second one to the first and returns the result.
Category: DBMS
Database Management Systems
TIMEDIFF() vs SUBTIME() in MySQL: What’s the Difference?
You might have noticed that MySQL has a TIMEDIFF() function and a SUBTIME() function. And you may have noticed that in many cases, they both return the same result. So you may be wondering what the difference is between these two functions?
Let’s find out.
TIMEDIFF() Examples – MySQL
The MySQL TIMEDIFF() function returns the difference between two time or datetime values.
The way it works is, you provide the two values to compare, and TIMEDIFF() subtracts the second value from the first, then returns the result as a time value.
TIME() Examples – MySQL
When working with MySQL, you can use the TIME() function to extract the time part from a time or datetime value.
The way it works is, you pass the time/datetime expression in as an argument, and TIME() will return the time part.
SUBTIME() Examples – MySQL
In MySQL, you can use the SUBTIME() function to subtract a time value from a time or datetime expression.
The way it works is, you provide two arguments; the first is a time or datetime value, and the second is a time value. The SUBTIME() function then subtracts the second argument from the first one and returns the result.
STR_TO_DATE() Examples – MySQL
The MySQL STR_TO_DATE() function allows you to build a date value from the various date parts.
When using this function, you provide a string of the various date parts, and a second argument that specifies the format that the date is provided in.
SEC_TO_TIME() Examples – MySQL
When using MySQL, you can use the SEC_TO_TIME() function to build a time value based on a given number of seconds. Basically, you provide the number of seconds as an argument, and it will convert that to a time value.
Here’s how it works.
QUARTER() Examples – MySQL
In MySQL, the QUARTER() function returns the quarter of the year of a given date.
This function accepts one argument – the date to extract the quarter from.
Full List of Locales in MySQL
This post contains a full list of locales supported by MySQL 8.0 that can be used as a value for the lc_time_names system variable. This locale controls the language used to display day and month names and abbreviations.
The following table lists these locale values, along with the language and region. These may be different to those supported by your operating system.
To set the locale, see How to Set the Locale for the Current Connection in MySQL.
How to Set the Locale for the Current Connection in MySQL
There are certain date functions in MySQL that the return a day name or month name. In particular, I’m referring to the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. These might return a value of say, November, or Monday, depending on the query being used. But the results could just as easily be returned in a different language if required.
The language that these functions use for their return value is derived from the lc_time_names system variable. You can view the value of this variable or set its SESSION value so that the results of these functions is in the desired language/locale.
Locale names have language and region subtags listed by the Internet Assigned Numbers Authority (IANA). Examples include en_US for English – United States, en_NZ for English – New Zealand, or es_PA for Spanish – Panama, etc (for a list of locales supported by MySQL, see Full List of Locales in MySQL).
In this article, I’ll show you how to find the current locale for your connection, change it, and then see how it affects the results of a query. I also show you a function that’s immune to this setting (but don’t worry, this function allows you to specify the locale).