MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Date and Time Operations

From WikiOD

Date arithmetic[edit | edit source]

NOW() + INTERVAL 1 DAY  -- This time tomorrow

CURDATE() - INTERVAL 4 DAY -- Midnight 4 mornings ago

Show the mysql questions stored that were asked 3 to 10 hours ago (180 to 600 minutes ago):

SELECT qId,askDate,minuteDiff  
FROM 
(   SELECT qId,askDate,
    TIMESTAMPDIFF(MINUTE,askDate,now()) as minuteDiff 
    FROM questions_mysql 
) xDerived 
WHERE minuteDiff BETWEEN 180 AND 600 
ORDER BY qId DESC 
LIMIT 50;

+----------+---------------------+------------+
| qId      | askDate             | minuteDiff |
+----------+---------------------+------------+
| 38546828 | 2016-07-23 22:06:50 |        182 |
| 38546733 | 2016-07-23 21:53:26 |        195 |
| 38546707 | 2016-07-23 21:48:46 |        200 |
| 38546687 | 2016-07-23 21:45:26 |        203 |
| ...      |                     |            |
+----------+---------------------+------------+

MySQL manual pages for TIMESTAMPDIFF().

Beware Do not try to use expressions like CURDATE() + 1 for date arithmetic in MySQL. They don't return what you expect, especially if you're accustomed to the Oracle database product. Use CURDATE() + INTERVAL 1 DAY instead.

SYSDATE(), NOW(), CURDATE()[edit | edit source]

  SELECT SYSDATE();

This function returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. It returns the date and time in the current time zone.

  SELECT NOW();

This function is a synonym for SYSDATE().

  SELECT CURDATE();

This function returns the current date, without any time, as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. It returns the date in the current time zone.

Now()[edit | edit source]

Select Now();

Shows the current server date and time.

Update `footable` set mydatefield = Now();

This will update the field mydatefield with current server date and time in server's configured timezone, e.g.

'2016*07*21 12:00:00'

Testing against a date range[edit | edit source]

Although it is very tempting to use BETWEEN ... AND ... for a date range, it is problematical. Instead, this pattern avoids most problems:

WHERE x >= '2016-02-25'
  AND x  < '2016-02-25' + INTERVAL 5 DAY

Advantages:

  • BETWEEN is 'inclusive' thereby including the final date or second.
  • 23:59:59 is clumsy and wrong if you have microsecond resolution on a DATETIME.
  • This pattern avoid dealing with leap years and other data calculations.
  • It works whether x is DATE, DATETIME or TIMESTAMP.

Extract Date from Given Date or DateTime Expression[edit | edit source]

SELECT DATE('2003-12-31 01:02:03');

The output will be:

2003-12-31

Using an index for a date and time lookup[edit | edit source]

Many real-world database tables have many rows with DATETIME OR TIMESTAMP column values spanning a lot of time, including years or even decades. Often it's necessary to use a WHERE clause to retrieve some subset of that timespan. For example, we might want to retrieve rows for the date 1-September-2016 from a table.

An inefficient way to do that is this:

 WHERE DATE(x) = '2016-09-01'   /* slow! */

It's inefficient because it applies a function -- DATE() -- to the values of a column. That means MySQL must examine each value of x, and an index cannot be used.

A better way to do the operation is this

 WHERE x >= '2016-09-01'
   AND x <  '2016-09-01' + INTERVAL 1 DAY

This selects a range of values of x lying anywhere on the day in question, up until but not including (hence <) midnight on the next day.

If the table has an index on the x column, then the database server can perform a range scan on the index. That means it can quickly find the first relevant value of x, and then scan the index sequentially until it finds the last relevant value. An index range scan is much more efficient than the full table scan required by DATE(x) = '2016-09-01.

Don't be tempted to use this, even though it looks more efficient.

  WHERE x BETWEEN '2016-09-01' AND '2016-09-01' + INTERVAL 1 DAY  /*  wrong! */

It has the same efficiency as the range scan, but it will select rows with values of x falling exactly at midnight on 2-Sept-2016, which is not what you want.

Credit:Stack_Overflow_Documentation