SQL Date & Time Functions
Generate date and time queries for PostgreSQL, MySQL, SQLite, and SQL Server. Pick a database and operation — get copy-ready SQL instantly.
-- Select a database and operation above
How to Use This Tool
Select your target database from the tabs — PostgreSQL, MySQL, SQLite, or SQL Server — then choose an operation from the dropdown. The tool generates a practical, copy-ready SQL query with realistic column names and values. Every query uses the correct syntax for your chosen database, including the right function names, interval syntax, and format specifiers.
This is particularly useful when switching between databases. Date handling is one of the least portable parts of SQL — the same operation often requires completely different functions and syntax across databases. Instead of searching documentation, pick your database and operation, copy the query, and adapt it to your schema.
Quick Reference: Current Date & Time
| Operation | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Current date + time | NOW() | NOW() | DATETIME('now') | GETDATE() |
| Current date only | CURRENT_DATE | CURDATE() | DATE('now') | CAST(GETDATE() AS DATE) |
| Current time only | CURRENT_TIME | CURTIME() | TIME('now') | CAST(GETDATE() AS TIME) |
| Current UTC | NOW() AT TIME ZONE 'UTC' | UTC_TIMESTAMP() | DATETIME('now') | GETUTCDATE() |
| Current epoch (seconds) | EXTRACT(EPOCH FROM NOW()) | UNIX_TIMESTAMP() | strftime('%s','now') | DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) |
Extract Date Parts
| Part | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Year | EXTRACT(YEAR FROM d) | YEAR(d) | strftime('%Y', d) | YEAR(d) |
| Month | EXTRACT(MONTH FROM d) | MONTH(d) | strftime('%m', d) | MONTH(d) |
| Day | EXTRACT(DAY FROM d) | DAY(d) | strftime('%d', d) | DAY(d) |
| Hour | EXTRACT(HOUR FROM d) | HOUR(d) | strftime('%H', d) | DATEPART(HOUR, d) |
| Day of week | EXTRACT(DOW FROM d) | DAYOFWEEK(d) | strftime('%w', d) | DATEPART(WEEKDAY, d) |
| Week number | EXTRACT(WEEK FROM d) | WEEK(d) | strftime('%W', d) | DATEPART(WEEK, d) |
| Quarter | EXTRACT(QUARTER FROM d) | QUARTER(d) | CAST((strftime('%m',d)+2)/3 AS INT) | DATEPART(QUARTER, d) |
Add & Subtract Intervals
| Operation | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Add 30 days | d + INTERVAL '30 days' | DATE_ADD(d, INTERVAL 30 DAY) | DATE(d, '+30 days') | DATEADD(DAY, 30, d) |
| Subtract 3 months | d - INTERVAL '3 months' | DATE_SUB(d, INTERVAL 3 MONTH) | DATE(d, '-3 months') | DATEADD(MONTH, -3, d) |
| Add 2 hours | d + INTERVAL '2 hours' | DATE_ADD(d, INTERVAL 2 HOUR) | DATETIME(d, '+2 hours') | DATEADD(HOUR, 2, d) |
| Add 1 year | d + INTERVAL '1 year' | DATE_ADD(d, INTERVAL 1 YEAR) | DATE(d, '+1 year') | DATEADD(YEAR, 1, d) |
Date Differences
| Unit | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Days | end_date - start_date | DATEDIFF(end, start) | JULIANDAY(end) - JULIANDAY(start) | DATEDIFF(DAY, start, end) |
| Months | EXTRACT(YEAR FROM AGE(e,s))*12 + EXTRACT(MONTH FROM AGE(e,s)) | TIMESTAMPDIFF(MONTH, s, e) | — | DATEDIFF(MONTH, s, e) |
| Seconds | EXTRACT(EPOCH FROM e - s) | TIMESTAMPDIFF(SECOND, s, e) | strftime('%s',e) - strftime('%s',s) | DATEDIFF(SECOND, s, e) |
| Full age | AGE(end_date, start_date) | TIMESTAMPDIFF(YEAR, s, e) | — | DATEDIFF(YEAR, s, e) |
Format Date as String
| Format | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| YYYY-MM-DD | TO_CHAR(d, 'YYYY-MM-DD') | DATE_FORMAT(d, '%Y-%m-%d') | strftime('%Y-%m-%d', d) | FORMAT(d, 'yyyy-MM-dd') |
| MM/DD/YYYY | TO_CHAR(d, 'MM/DD/YYYY') | DATE_FORMAT(d, '%m/%d/%Y') | strftime('%m/%d/%Y', d) | FORMAT(d, 'MM/dd/yyyy') |
| Mon DD, YYYY | TO_CHAR(d, 'Mon DD, YYYY') | DATE_FORMAT(d, '%b %d, %Y') | — | FORMAT(d, 'MMM dd, yyyy') |
| HH:MM:SS | TO_CHAR(d, 'HH24:MI:SS') | DATE_FORMAT(d, '%H:%i:%s') | strftime('%H:%M:%S', d) | FORMAT(d, 'HH:mm:ss') |
Date Types Compared
Every database handles date storage differently. Choosing the right type affects precision, timezone behavior, and storage size.
| Database | Type | Stores | Timezone? | Range | Notes |
|---|---|---|---|---|---|
| PostgreSQL | TIMESTAMP | Date + time | No | 4713 BC – 294276 AD | Naive — no timezone conversion |
| PostgreSQL | TIMESTAMPTZ | Date + time | Yes | 4713 BC – 294276 AD | Stored as UTC, displayed in session tz. Preferred |
| MySQL | DATETIME | Date + time | No | 1000 – 9999 | Literal value, no tz conversion |
| MySQL | TIMESTAMP | Date + time | Yes | 1970 – 2038 | Stored UTC, converted to session tz |
| SQL Server | DATETIME2 | Date + time | No | 0001 – 9999 | 100ns precision. Preferred over DATETIME |
| SQL Server | DATETIMEOFFSET | Date + time + offset | Yes | 0001 – 9999 | Stores tz offset with the value |
| SQLite | TEXT / REAL / INTEGER | Varies | No | Unlimited | No native date type — uses ISO text, Julian day, or Unix epoch |
Common Patterns & Recipes
These are real-world queries you'll reach for repeatedly — filtering by date range, calculating ages, grouping by time period, and finding the most recent record per group.
Filter rows from the last 30 days
Calculate age from birth date
Group by month
Most recent record per group
Frequently Asked Questions
How do I get the current date in SQL?
PostgreSQL: CURRENT_DATE or NOW(). MySQL: CURDATE() or NOW(). SQL Server: GETDATE() or SYSDATETIME(). SQLite: DATE('now'). Use NOW()/GETDATE() when you need both date and time; use CURRENT_DATE/CURDATE() for date only.
How do I calculate the difference between two dates in SQL?
PostgreSQL subtracts dates directly: end_date - start_date returns an integer (days) for date types, or an interval for timestamps. MySQL uses DATEDIFF(end, start) for days or TIMESTAMPDIFF(unit, start, end) for other units. SQL Server uses DATEDIFF(unit, start, end). SQLite uses JULIANDAY(end) - JULIANDAY(start) for days.
How do I add days or months to a date in SQL?
PostgreSQL: d + INTERVAL '30 days'. MySQL: DATE_ADD(d, INTERVAL 30 DAY). SQL Server: DATEADD(DAY, 30, d). SQLite: DATE(d, '+30 days'). Replace "days" with "months", "years", or "hours" as needed — the syntax varies by database.
What is the difference between TIMESTAMP and DATETIME?
In MySQL, TIMESTAMP is stored as UTC and auto-converts to your session timezone (range: 1970–2038), while DATETIME stores the literal value without timezone conversion (range: 1000–9999). In PostgreSQL, use TIMESTAMPTZ for timezone-aware storage — it stores everything as UTC internally. In SQL Server, prefer DATETIME2 over the older DATETIME for better precision and range.
How do I format a date as a string in SQL?
PostgreSQL: TO_CHAR(d, 'YYYY-MM-DD'). MySQL: DATE_FORMAT(d, '%Y-%m-%d'). SQL Server: FORMAT(d, 'yyyy-MM-dd'). SQLite: strftime('%Y-%m-%d', d). Each database uses different format specifiers — check the reference tables above for a full mapping.
How do I convert between Unix epoch and dates in SQL?
To get epoch: PostgreSQL uses EXTRACT(EPOCH FROM timestamp), MySQL uses UNIX_TIMESTAMP(date), SQLite uses strftime('%s', date). To convert from epoch: PostgreSQL uses TO_TIMESTAMP(epoch), MySQL uses FROM_UNIXTIME(epoch), SQLite uses DATETIME(epoch, 'unixepoch'), SQL Server uses DATEADD(SECOND, epoch, '1970-01-01').