MySQL Date and Time Basic Questions

SELECT NOW();              -- Current date and time (e.g., 2025-06-13 20:15:00)
SELECT CURDATE();          -- Current date only
SELECT CURTIME();          -- Current time only
SELECT UTC_TIMESTAMP();    -- Current UTC date and time

-- Format a datetime field
SELECT DATE_FORMAT(NOW(), '%d-%m-%Y');       -- Output: 13-06-2025
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');      -- Output: June 13, 2025
SELECT DATE_FORMAT(NOW(), '%b-%Y');          -- Output: Jun-2025

Common format codes:

%d - Day (01-31)
%m - Month (01-12)
%b / %M - Month name short/long
%Y - Year (4 digits)
%H:%i:%s - Time (Hours:Minutes:Seconds)

SELECT YEAR(NOW());        -- 2025
SELECT MONTH(NOW());       -- 6
SELECT DAY(NOW());         -- 13
SELECT HOUR(NOW());        -- 20

-- Add/Subtract Days, Months, Years
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);         -- Add 7 days
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);       -- Subtract 1 month
SELECT NOW() + INTERVAL 1 HOUR;                 -- Add 1 hour

SELECT DATEDIFF('2025-06-20', '2025-06-13');      -- Days between: 7
SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', NOW());  -- Age calculation
SELECT TIMEDIFF('12:30:00', '11:00:00');          -- Time difference: 01:30:00

SELECT * FROM orders WHERE order_date = CURDATE();       -- Today
SELECT * FROM orders WHERE order_date >= '2025-01-01';

-- Orders from last 30 days
SELECT * FROM orders 
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-10';

-- From Unix timestamp to readable date
SELECT FROM_UNIXTIME(1718293800);    -- Output: 2025-06-13 15:00:00

-- To Unix timestamp
SELECT UNIX_TIMESTAMP('2025-06-13 15:00:00');    -- Output: 1718293800

-- Convert UTC to IST (Asia/Kolkata)
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Kolkata');

-- Get First/Last day of month
SELECT LAST_DAY(NOW());
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');  -- First day of month

-- Week related
SELECT WEEK(NOW());        -- Week number
SELECT DAYOFWEEK(NOW());   -- 1 (Sunday) to 7 (Saturday)