Practice Questions — Date and Time Functions

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer VARCHAR(50),
  order_date DATE,
  amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (1, 'Aarav Sharma',  '2026-04-15', 1200),
  (2, 'Priya Nair',    '2026-04-02',  850),
  (3, 'Rohan Mehta',   '2026-03-25', 2400),
  (4, 'Meera Krishnan','2026-03-10',  450),
  (5, 'Vikram Singh',  '2026-02-28', 3100);

SELECT *
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY
ORDER BY order_date DESC;

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), dob DATE);
INSERT INTO students VALUES
  (1, 'Ishaan Gupta',  '2008-11-12'),
  (2, 'Kavya Reddy',   '2006-02-29'),
  (3, 'Arjun Pillai',  '2010-04-17'),
  (4, 'Neha Choudhary','2004-07-22'),
  (5, 'Diya Bhatt',    '2000-01-05');

SELECT name, dob,
       TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM students
ORDER BY age DESC;

CREATE TABLE sales (id INT PRIMARY KEY, sold_at DATETIME, amount DECIMAL(10,2));
INSERT INTO sales VALUES
  (1, '2026-01-10 10:22:00', 1200),
  (2, '2026-01-25 15:40:00',  800),
  (3, '2026-02-03 09:10:00', 2200),
  (4, '2026-02-28 18:05:00', 1500),
  (5, '2026-03-12 11:30:00', 3100),
  (6, '2026-03-29 20:00:00',  950),
  (7, '2026-04-05 13:15:00', 1800),
  (8, '2026-04-15 16:45:00', 2650);

SELECT DATE_FORMAT(sold_at, '%Y-%m') AS month,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM sales
WHERE sold_at >= '2026-01-01' AND sold_at < '2027-01-01'
GROUP BY month
ORDER BY month;

CREATE TABLE sessions (user_id INT, last_login DATETIME);
INSERT INTO sessions VALUES
  (101, '2026-04-15 09:12:00'),
  (102, '2026-03-05 14:20:00'),
  (103, '2026-04-10 11:00:00'),
  (104, '2026-01-20 08:30:00'),
  (105, '2026-03-14 22:15:00');

SELECT user_id,
       MAX(last_login)                           AS last_seen,
       DATEDIFF(CURDATE(), MAX(last_login))      AS days_inactive
FROM sessions
GROUP BY user_id
HAVING days_inactive > 30
ORDER BY days_inactive DESC;

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), created_at DATETIME);
INSERT INTO users VALUES
  (1,'Aarav Sharma',  '2026-04-13 10:00:00'),  -- Monday
  (2,'Priya Nair',    '2026-04-14 09:30:00'),  -- Tuesday
  (3,'Rohan Mehta',   '2026-04-15 18:10:00'),  -- Wednesday
  (4,'Ananya Iyer',   '2026-04-16 07:22:00'),  -- Thursday
  (5,'Vikram Singh',  '2026-04-10 23:00:00'),  -- Friday
  (6,'Meera Krishnan','2026-04-11 12:00:00'),  -- Saturday
  (7,'Diya Bhatt',    '2026-04-12 19:30:00');  -- Sunday

SELECT DAYNAME(created_at) AS day_of_week,
       COUNT(*)             AS signups
FROM users
GROUP BY day_of_week
ORDER BY FIELD(day_of_week,
               'Monday','Tuesday','Wednesday','Thursday',
               'Friday','Saturday','Sunday');

CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, order_date DATE);
INSERT INTO orders VALUES
  (1, 101, '2025-01-10'),
  (2, 101, '2025-07-22'),
  (3, 101, '2026-03-05'),
  (4, 102, '2025-11-02'),
  (5, 102, '2026-02-14'),
  (6, 103, '2026-04-01');

SELECT customer_id,
       MIN(order_date)                                       AS first_order,
       MAX(order_date)                                       AS last_order,
       TIMESTAMPDIFF(MONTH, MIN(order_date), MAX(order_date)) AS tenure_months
FROM orders
GROUP BY customer_id
ORDER BY tenure_months DESC;

CREATE TABLE raw_upload (id INT PRIMARY KEY, raw_date VARCHAR(30));
INSERT INTO raw_upload VALUES
  (1, '16-04-2026'),
  (2, '05/04/2026'),
  (3, 'April 12, 2026'),
  (4, '2026.04.09'),
  (5, 'not a date');

SELECT id, raw_date,
       CASE
         WHEN raw_date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}{{CODING_CHALLENGES_HTML}}#39;    THEN STR_TO_DATE(raw_date, '%d-%m-%Y')
         WHEN raw_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}{{CODING_CHALLENGES_HTML}}#39;    THEN STR_TO_DATE(raw_date, '%d/%m/%Y')
         WHEN raw_date REGEXP '^[A-Za-z]+ [0-9]+, [0-9]{4}{{CODING_CHALLENGES_HTML}}#39;    THEN STR_TO_DATE(raw_date, '%M %d, %Y')
         WHEN raw_date REGEXP '^[0-9]{4}\\.[0-9]{2}\\.[0-9]{2}{{CODING_CHALLENGES_HTML}}#39; THEN STR_TO_DATE(raw_date, '%Y.%m.%d')
         ELSE NULL
       END AS parsed_date
FROM raw_upload;

CREATE TABLE webinars (id INT PRIMARY KEY, topic VARCHAR(60), starts_utc DATETIME);
INSERT INTO webinars VALUES
  (1,'Python Masterclass','2026-04-20 13:30:00'),
  (2,'SQL Interview Prep','2026-04-22 14:00:00'),
  (3,'Web Dev Bootcamp',  '2026-04-25 03:00:00');

SELECT topic,
       starts_utc                                          AS utc,
       CONVERT_TZ(starts_utc,'+00:00','+05:30')            AS ist,
       CONVERT_TZ(starts_utc,'+00:00','+08:00')            AS sg,
       CONVERT_TZ(starts_utc,'+00:00','-04:00')            AS ny,
       DATE_FORMAT(CONVERT_TZ(starts_utc,'+00:00','+05:30'),
                   '%W, %d %b %h:%i %p')                   AS pretty_ist
FROM webinars
ORDER BY starts_utc;