Dates, times and booleans
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Dates and times have their own column types in MySQL, and they all use one fixed format: year first, then month, then day, as in '2025-08-15'. True/false values have no separate type at all; MySQL stores them as the numbers 1 and 0.
DATE and the 'YYYY-MM-DD' format
DATE stores a calendar day with no time, written as text in the form 'YYYY-MM-DD'. MySQL refuses days that do not exist, sorts dates correctly and can do date maths.
DATE stores a calendar day with no time: a birthday, a journey date, an exam date. You write it as text in the form 'YYYY-MM-DD': four-digit year, two-digit month, two-digit day. It holds days from 1000-01-01 to 9999-12-31 in 3 bytes.
Example 1 · DATE columns and wrong formats
CREATE TABLE train_journeys ( pnr CHAR(10), passenger VARCHAR(40), journey_date DATE);
INSERT INTO train_journeys (pnr, passenger, journey_date) VALUES ('4521367890', 'Asha Rao', '2025-08-15'), ('4521367891', 'Ravi Kumar', '2025-10-02'), ('4521367892', 'Meena Iyer', '2025-8-5');
SELECT * FROM train_journeys ORDER BY journey_date;
INSERT INTO train_journeys (pnr, passenger, journey_date) VALUES ('4521367893', 'Kabir Singh', '15-08-2025');+------------+------------+--------------+ | pnr | passenger | journey_date | +------------+------------+--------------+ | 4521367892 | Meena Iyer | 2025-08-05 | | 4521367890 | Asha Rao | 2025-08-15 | | 4521367891 | Ravi Kumar | 2025-10-02 | +------------+------------+--------------+ ERROR 1292 (22007): Incorrect date value: '15-08-2025' for column 'journey_date' at row 1
What this code does
ORDER BY journey_datesorts by the real calendar order.'2025-8-5'was accepted and stored in the full form2025-08-05.'15-08-2025'is day-month-year, so MySQL refuses it with error 1292. Writing'2025-02-30'gives the same error, because that day does not exist.
Why a date type instead of VARCHAR? MySQL checks every date and refuses ones that do not exist, such as 30 February. It also sorts dates correctly and can do date maths, such as days between two dates. Text like '15-08-2025' would sort by day number and accept '31-02-2025' without a word.
The Indian habit of day-month-year (15-08-2025) is the most common mistake: MySQL reads the first part as the year and answers ERROR 1292 (22007): Incorrect date value: '15-08-2025' for column 'journey_date' at row 1.
Fix the error
Fix the date
Read the real error, then pick the line that fixes it.
The journey date is refused.
Error
ERROR 1292 (22007): Incorrect date value: '15-08-2025' for column 'journey_date' at row 1
'2025-8-5' is stored as 2025-08-05. Write the full form anyway; it is what every app and report expects.TIME and DATETIME
DATETIME stores a date and a time together as 'YYYY-MM-DD HH:MM:SS' and keeps exactly what you give it, with no time zone change. TIME stores a time of day or a length of time.
TIME stores a time of day or a length of time as 'HH:MM:SS', such as a train's departure '16:55:00'. Because it can also be a duration, it allows values from -838:59:59 to 838:59:59, so '25:00:00' is accepted.
Example 2 · TIME for departures, DATETIME for bookings
CREATE TABLE train_bookings ( train_no INT, departs_at TIME, run_time TIME, booked_at DATETIME);
INSERT INTO train_bookings (train_no, departs_at, run_time, booked_at) VALUES (12627, '19:20:00', '38:40:00', '2025-07-01 10:02:45'), (12951, '16:55:00', '15:55:00', '2025-07-01');
SELECT * FROM train_bookings ORDER BY train_no;+----------+------------+----------+---------------------+ | train_no | departs_at | run_time | booked_at | +----------+------------+----------+---------------------+ | 12627 | 19:20:00 | 38:40:00 | 2025-07-01 10:02:45 | | 12951 | 16:55:00 | 15:55:00 | 2025-07-01 00:00:00 | +----------+------------+----------+---------------------+
What this code does
run_timeis a duration, so38:40:00(38 hours 40 minutes) is fine in aTIMEcolumn.- The second booking gave only a date, so the
DATETIMEvalue became2025-07-01 00:00:00: midnight, not "no time".
DATETIME stores a date and a time together as 'YYYY-MM-DD HH:MM:SS', such as when a ticket was booked or when a cinema show starts. It covers years 1000 to 9999 and stores exactly what you give it, with no time zone change.
If you insert only a date into a DATETIME column, the time becomes midnight: '2025-08-15' is stored as 2025-08-15 00:00:00. That matters later when you search for "everything on 15 August".
(3) or (6) to keep fractions of a second: DATETIME(3) stores milliseconds, useful for payment events. Plain DATETIME keeps whole seconds.TIMESTAMP and time zones
TIMESTAMP stores a date and time as UTC and converts it to the connection's time zone when read. It only covers 1970 to January 2038, so far-future dates need DATETIME.
TIMESTAMP also stores a date and time, but it saves the moment in UTC (world standard time) and converts it to the connection's time zone when you read it.
Example 3 · TIMESTAMP converts, DATETIME does not
CREATE TABLE upi_payments ( txn_id VARCHAR(12), paid_dt DATETIME, paid_ts TIMESTAMP);
SET time_zone = '+05:30';INSERT INTO upi_payments (txn_id, paid_dt, paid_ts) VALUES ('UPI1001', '2025-08-15 09:30:00', '2025-08-15 09:30:00');SELECT * FROM upi_payments;
SET time_zone = '+00:00';SELECT * FROM upi_payments;
INSERT INTO upi_payments (txn_id, paid_dt, paid_ts) VALUES ('UPI1002', '2040-01-01 00:00:00', '2040-01-01 00:00:00');+---------+---------------------+---------------------+ | txn_id | paid_dt | paid_ts | +---------+---------------------+---------------------+ | UPI1001 | 2025-08-15 09:30:00 | 2025-08-15 09:30:00 | +---------+---------------------+---------------------+ +---------+---------------------+---------------------+ | txn_id | paid_dt | paid_ts | +---------+---------------------+---------------------+ | UPI1001 | 2025-08-15 09:30:00 | 2025-08-15 04:00:00 | +---------+---------------------+---------------------+ ERROR 1292 (22007): Incorrect datetime value: '2040-01-01 00:00:00' for column 'paid_ts' at row 1
What this code does
- Both columns got 09:30 while the connection was in Indian time (+05:30). Read from a connection in UTC, the
TIMESTAMPshows 04:00, the same moment in UTC; theDATETIMEstill shows 09:30. - A date in 2040 is past the
TIMESTAMPlimit (19 January 2038, UTC), so the insert fails with error 1292.DATETIMEalone would have accepted it.
A value written as 09:30 in India (UTC+05:30) is read back as 04:00 by a connection set to UTC. DATETIME never converts.
Storing UTC makes TIMESTAMP good for "when did this happen" columns in an app used across time zones: two users in Mumbai and Dubai see the same moment in their own local time. The connection's zone comes from the server, or from SET time_zone = '+05:30';. The catch is its exact range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC. Rule of thumb: DATETIME for planned dates and anything far in the future, such as exam schedules, show times and due dates; TIMESTAMP for record times such as created_at, if 2038 is not a concern for that table.
BOOLEAN is TINYINT(1) in MySQL
BOOLEAN (or BOOL) is the type name for yes/no values. In MySQL it is TINYINT(1): TRUE is stored as 1 and FALSE as 0, and the column accepts any small number.
BOOLEAN (or BOOL) is the type for yes/no values such as is_fragile, is_paid or is_active. MySQL has no real boolean storage: BOOLEAN is a name for TINYINT(1), a small integer. TRUE is stored as 1 and FALSE as 0.
Example 4 · BOOLEAN is really a small number
CREATE TABLE product_flags ( item VARCHAR(30), is_fragile BOOLEAN, is_gift BOOL);
DESCRIBE product_flags;
INSERT INTO product_flags (item, is_fragile, is_gift) VALUES ('Redmi Note 13', TRUE, TRUE), ('Cotton Kurta', FALSE, TRUE), ('Buggy row', 5, 0);
SELECT item, is_fragile, is_fragile = TRUE, IF(is_fragile, 'fragile', 'sturdy') AS labelFROM product_flagsORDER BY item;+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | item | varchar(30) | YES | | NULL | | | is_fragile | tinyint(1) | YES | | NULL | | | is_gift | tinyint(1) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ +---------------+------------+-------------------+---------+ | item | is_fragile | is_fragile = TRUE | label | +---------------+------------+-------------------+---------+ | Buggy row | 5 | 0 | fragile | | Cotton Kurta | 0 | 0 | sturdy | | Redmi Note 13 | 1 | 1 | fragile | +---------------+------------+-------------------+---------+
What this code does
DESCRIBEshows both columns astinyint(1).TRUEandFALSEwere stored as1and0, and the buggy5was stored too.IF(condition, a, b)givesawhen the condition is true. For5,IFsaysfragile(any non-zero is true), butis_fragile = TRUEis0, because it compares with 1. Store only 0 and 1 to avoid this split.
The column accepts any number from -128 to 127, so a buggy insert of 5 is stored as 5.
The safe habits: store only 0 and 1, write conditions as WHERE is_fragile or WHERE is_fragile = 1, and name the column as a yes/no question (is_, has_). The (1) in tinyint(1) is only a display hint that tells tools "this is a boolean".
Predict the output
Predict the values
A buggy row stored the number 5 in a BOOLEAN column. What do these two expressions give?
CURDATE() and NOW()
CURDATE() returns today's date and NOW() returns the current date and time, both from the server's clock. They are used to stamp a row, often as a DEFAULT value.
CURDATE() returns today's date and NOW() returns the current date and time, both from the server's clock in the connection's time zone. CURTIME() returns just the time. They are how you stamp a row with "today" or "right now".
Example 5 · CURDATE(), NOW() and default timestamps
SELECT CURDATE(), CURTIME(), NOW();
CREATE TABLE app_logins ( username VARCHAR(30), login_day DATE DEFAULT (CURDATE()), login_at DATETIME DEFAULT CURRENT_TIMESTAMP);
INSERT INTO app_logins (username) VALUES ('asha.rao');SELECT * FROM app_logins;+------------+-----------+---------------------+ | CURDATE() | CURTIME() | NOW() | +------------+-----------+---------------------+ | 2026-10-16 | 07:26:30 | 2026-10-16 07:26:30 | +------------+-----------+---------------------+ +----------+------------+---------------------+ | username | login_day | login_at | +----------+------------+---------------------+ | asha.rao | 2026-10-16 | 2026-10-16 07:26:30 | +----------+------------+---------------------+
What this code does
- Your output shows your own date and time; that is why this is a sample.
- The insert gave only
username, so the two defaults filled in today and now.DEFAULT (CURDATE())needs the brackets and MySQL 8.0.13 or later.
The most common use is a default value: DEFAULT CURRENT_TIMESTAMP on a DATETIME or TIMESTAMP column fills in the current moment when an insert leaves the column out. For a DATE column, write the function in brackets: DEFAULT (CURDATE()), which works from MySQL 8.0.13.
Results of these functions change every second, so the outputs below are samples: yours will show your date and time.
NOW() returns the same value for the whole statement, even if it runs for several seconds, so all rows inserted by one statement get the same time.Next step
Check your understanding
11 quick questions