> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL Date and Time Functions

> Reference guide for MySQL date and time functions: NOW, CURDATE, DATE_FORMAT, DATEDIFF, DATE_ADD, EXTRACT, STR_TO_DATE, UNIX_TIMESTAMP, and common date manipulation patterns.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

MySQL provides a full suite of date and time functions. The main types you'll work with are `DATE` (date only), `TIME` (time only), `DATETIME` (date + time, no timezone), and `TIMESTAMP` (date + time, stored in UTC, displayed in session timezone). See [Timestamps and Time Zones in MySQL](/guides/timestamps-timezones) for timezone handling details.

## Getting the Current Date and Time

```sql theme={null}
SELECT NOW();            -- '2024-06-15 14:30:00'  (DATETIME, session timezone)
SELECT SYSDATE();        -- same, but evaluated at call time, not statement start
SELECT CURRENT_TIMESTAMP; -- synonym for NOW()

SELECT CURDATE();        -- '2024-06-15'  (DATE only)
SELECT CURTIME();        -- '14:30:00'    (TIME only)
SELECT UTC_TIMESTAMP();  -- '2024-06-15 21:30:00'  (UTC)
```

`NOW()` is evaluated once at the start of a statement — all rows in the same INSERT get the same timestamp. `SYSDATE()` is evaluated each time it's called.

## Formatting Dates

`DATE_FORMAT` converts a date or datetime to a string using a format string:

```sql theme={null}
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');          -- '2024-06-15'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');          -- '15/06/2024'
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');         -- 'June 15, 2024'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2024-06-15 14:30:00'
```

Common format specifiers:

| Specifier | Meaning                                | Example    |
| :-------- | :------------------------------------- | :--------- |
| `%Y`      | 4-digit year                           | `2024`     |
| `%y`      | 2-digit year                           | `24`       |
| `%m`      | Month (01–12)                          | `06`       |
| `%M`      | Month name                             | `June`     |
| `%d`      | Day (01–31)                            | `15`       |
| `%H`      | Hour (00–23)                           | `14`       |
| `%i`      | Minute (00–59)                         | `30`       |
| `%s`      | Second (00–59)                         | `00`       |
| `%W`      | Weekday name                           | `Saturday` |
| `%w`      | Weekday (0=Sunday)                     | `6`        |
| `%j`      | Day of year (001–366)                  | `167`      |
| `%U`      | Week number (0–53, week starts Sunday) | `24`       |

## Parsing Strings to Dates

`STR_TO_DATE` converts a string to a date using the same format specifiers:

```sql theme={null}
SELECT STR_TO_DATE('15/06/2024', '%d/%m/%Y');         -- '2024-06-15'
SELECT STR_TO_DATE('June 15, 2024', '%M %d, %Y');     -- '2024-06-15'
SELECT STR_TO_DATE('2024-06-15 14:30:00', '%Y-%m-%d %H:%i:%s');  -- DATETIME
```

Returns NULL if the string doesn't match the format.

## Date Arithmetic

**DATE\_ADD / DATE\_SUB** — add or subtract an interval:

```sql theme={null}
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);        -- 7 days from now
SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH);      -- 3 months from now
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);       -- 1 year ago
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- '2024-02-29' (end of Feb)
```

Interval units: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `WEEK`, `MONTH`, `QUARTER`, `YEAR`.

Shorthand with `+` and `-` operators:

```sql theme={null}
SELECT NOW() + INTERVAL 1 DAY;
SELECT '2024-06-15' - INTERVAL 1 MONTH;
```

**DATEDIFF** — days between two dates:

```sql theme={null}
SELECT DATEDIFF('2024-12-31', '2024-06-15');  -- 199
SELECT DATEDIFF(NOW(), created_at) AS days_old FROM orders;
```

`DATEDIFF` only counts days, not time. For time-aware differences, use `TIMESTAMPDIFF`:

```sql theme={null}
SELECT TIMESTAMPDIFF(HOUR, '2024-06-15 08:00', '2024-06-15 14:30');  -- 6
SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_ago FROM events;
```

`TIMESTAMPDIFF(unit, from, to)` — note that `from` comes before `to`.

## Extracting Date Parts

**EXTRACT / YEAR / MONTH / DAY**:

```sql theme={null}
SELECT YEAR(NOW());     -- 2024
SELECT MONTH(NOW());    -- 6
SELECT DAY(NOW());      -- 15 (same as DAYOFMONTH)
SELECT HOUR(NOW());     -- 14
SELECT MINUTE(NOW());   -- 30

-- EXTRACT uses interval unit names
SELECT EXTRACT(YEAR FROM NOW());        -- 2024
SELECT EXTRACT(HOUR FROM NOW());        -- 14
SELECT EXTRACT(YEAR_MONTH FROM NOW());  -- 202406
```

**DAYOFWEEK / DAYOFYEAR / WEEK**:

```sql theme={null}
SELECT DAYOFWEEK(NOW());   -- 7  (1=Sunday, 7=Saturday)
SELECT WEEKDAY(NOW());     -- 5  (0=Monday, 6=Sunday)
SELECT DAYOFYEAR(NOW());   -- 167
SELECT WEEK(NOW());        -- 24  (week 0–53, Sunday-based)
SELECT YEARWEEK(NOW());    -- 202424
```

## Truncating to a Period

To group data by month, week, or day, truncate the datetime to the period boundary:

```sql theme={null}
-- Group orders by month
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

-- Group by week
SELECT YEARWEEK(created_at) AS week, COUNT(*) AS orders
FROM orders
GROUP BY week;

-- Truncate to start of month
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');  -- '2024-06-01'
```

## Unix Timestamps

```sql theme={null}
SELECT UNIX_TIMESTAMP();                          -- seconds since epoch (session timezone)
SELECT UNIX_TIMESTAMP('2024-06-15 14:30:00');     -- datetime to epoch (session timezone)
SELECT FROM_UNIXTIME(1718451000);                 -- epoch to DATETIME
SELECT FROM_UNIXTIME(1718451000, '%Y-%m-%d');     -- epoch to formatted string
```

## Common Patterns

Records from the last 30 days:

```sql theme={null}
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL 30 DAY;
```

Records from the current month:

```sql theme={null}
SELECT * FROM orders
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND created_at <  DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01');
```

Age from a birthdate:

```sql theme={null}
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age FROM users;
```

## Frequently Asked Questions

#### What's the difference between NOW() and SYSDATE()?

`NOW()` returns the timestamp when the statement began executing — all rows in a single INSERT or UPDATE get the same value. `SYSDATE()` returns the actual clock time at the moment it's called, so rows inserted in a loop get different values. For audit timestamps where consistency within a transaction matters, `NOW()` is almost always the right choice.

#### Why does DATE\_ADD with INTERVAL 1 MONTH behave oddly at month-end?

MySQL clamps to the last valid day. `DATE_ADD('2024-01-31', INTERVAL 1 MONTH)` returns `2024-02-29` (leap year) or `2024-02-28`. This is expected behavior — there's no "31st of February." If you need predictable month arithmetic, truncate to the start of the month first.

## Troubleshooting

| Problem                                              | Solution                                                                                                       |
| :--------------------------------------------------- | :------------------------------------------------------------------------------------------------------------- |
| `STR_TO_DATE` returns NULL                           | Format string doesn't match the input — verify each format specifier matches its corresponding input character |
| `DATEDIFF` returns 0 for timestamps on the same date | `DATEDIFF` ignores the time component — two rows on the same calendar date return 0                            |
| Date comparison misses rows at end of day            | `WHERE created_at <= '2024-06-15'` stops at midnight — use `< '2024-06-16'` to include the full day            |
| `TIMESTAMPDIFF` returns unexpected negative value    | Arguments are in wrong order — `TIMESTAMPDIFF(unit, from, to)`, not `(unit, to, from)`                         |
| `WEEK()` returns different numbers than expected     | Week numbering mode matters (Sunday vs Monday start) — use `YEARWEEK(date, 3)` for ISO 8601 week numbers       |

## See also

* [Timestamps and Time Zones in MySQL](/guides/timestamps-timezones) — the storage types that date functions operate on