Timestampdiff

---

• Syntax: TIMESTAMPDIFF(unit, begin, end)
• More on the definition is in Date & Time page

Calculate ages

---

• What if we want to calculate the ages of each person in the Persons table

create table

CREATE TABLE Persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL
);

insert rows

INSERT INTO Persons(full_name, date_of_birth)
VALUES('John Doe', '1990-01-01'),
      ('David Taylor', '1989-06-06'),
      ('Peter Drucker', '1985-03-02'),
      ('Lily Smith', '1992-05-05'),
      ('Mary William', '1995-12-01');

calculate ages

• Let’s calculate the ages up to 2017/01/01

SELECT 
    id,
    full_name,
    date_of_birth,
    TIMESTAMPDIFF(YEAR,
                date_of_birth,
                '2017-01-01') age
FROM
    persons;

• What if we want to calculate their ages up to NOW()
• Of course, just change the argument end

SELECT 
    id,
    full_name,
    date_of_birth,
    TIMESTAMPDIFF(YEAR,
                date_of_birth,
                NOW()) age
FROM
    persons;

Calculate Intervals

---

Of course all TIMESTAMPDIFF does is calculate intervals, so let’s use it in a somehow (appears to be) complicated code chunk to

• calculate the time it took for a ride to be completed in
• minutes and milliseconds.

SELECT
        TIMESTAMPDIFF(
                MINUTE,
                CAST('2021-01-01 14:01:05' AS TIMESTAMP),
                CAST('2021-01-01 12:22:23' AS TIMESTAMP)) AS minutes_difference,
        TIMESTAMPDIFF(
                MILLISECOND,
                CAST('2021-01-01 01:44:33' AS TIMESTAMP),
                CAST('2020-12-31 22:04:60' AS TIMESTAMP)) AS millisecond_difference;
        
Results:  
minutes_difference: 98
millisecond_difference: 13173000

Last N Seconds

---

• A common query is to look at values that occurred the last n seconds
• Here we can use both TIMESTAMPDIFF and TIMESTAMP and TIMESTAMP_SUB
• date is the column from the table
• Note: TIMESTAMPDIFF is the difference between two times, units are specified
• Note: CURRENT_TIMESTAMP is the datetime of now()
• Note: we make use of SUBQUERIES to create an ALIAS date

SELECT
          date,
          TIMESTAMPDIFF(SECOND,CURRENT_TIMESTAMP(), date) AS seconds_since
FROM
          (
          SELECT  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 SECOND) AS date
          UNION ALL
                ( SELECT  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 SECOND) AS date)
          UNION ALL
                ( SELECT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND) AS date)
          ) AS table_3
WHERE
          (TIMESTAMPDIFF(SECOND, CURRENT_TIMESTAMP(), date) <= 30)