SQL dates and times in PostgreSQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
PostgreSQL has no DATEDIFF, DATEADD or GETDATE. format() does
exist, but its first argument is the template string, so there is no overload you can hand a
date to first. Bring SQL over from SQL Server or MySQL and PostgreSQL rejects all four without
saying what to use instead.
Find your function on the left, what to use instead on the right, and open the guide for a worked version.
What does PostgreSQL use instead of SQL Server and MySQL date functions?
You need subtraction, the + operator, now() and
to_char() for almost all of them:
| You wrote | PostgreSQL says | Use instead | Guide |
|---|---|---|---|
DATEDIFF(day, start_date, end_date) | column "day" does not exist | end_date - start_date | DATEDIFF guide |
DATEADD(day, 30, hire_date) | column "day" does not exist | hire_date + 30 | DATEADD guide |
DATE_ADD(hire_date, INTERVAL 30 DAY) | syntax error at or near "30" | hire_date + 30 A date, the same as the DATEADD row above. Put INTERVAL '30 days' where the 30 is and you get a timestamp instead. | DATE_ADD in Postgres |
GETDATE() | function getdate() does not exist | now() A timestamptz. Swap in current_date and you compare against midnight, so you lose the later rows from that day. | GETDATE guide |
FORMAT(ordered_at, 'yyyy-MM') | function format(timestamp with time zone, unknown) does not exist | to_char(ordered_at, 'YYYY-MM') | Formatting guide |
DATE_FORMAT(ordered_at, '%Y-%m') | function date_format(timestamp with time zone, unknown) does not exist | to_char(ordered_at, 'YYYY-MM') | DATE_FORMAT in Postgres |
CONVERT(date, '2026-03-14') | column "date" does not exist | '2026-03-14'::date | String to date guide |
STR_TO_DATE('14/03/2026', '%d/%m/%Y') | function str_to_date(unknown, unknown) does not exist | to_date('14/03/2026', 'DD/MM/YYYY') | STR_TO_DATE in Postgres |
The middle column is the error PostgreSQL raises. The link under each spelling on the left goes to Microsoft’s or MySQL’s own documentation for that function.
Read those first two errors literally: PostgreSQL is looking for a column called
day. PostgreSQL resolves the arguments before it looks for a function, so it reads
the bare word day as a column name and reports the column it cannot find.
PostgreSQL does that on any ordinary f(a, b) call, whether or not the function
exists. Try abs(day), length(day), date_trunc(day,
start_date) or no_such_function(day) against job_history and
you get column "day" does not exist for each one. A few spellings have a grammar
of their own, and in those PostgreSQL takes the bare word as a value rather than as a column:
run EXTRACT(day FROM TIMESTAMP '2026-03-04 09:30') and you get 4,
and in xmlelement(name day, 1) PostgreSQL uses day as the tag name.
Add a column called day to the table and you get function
datediff(integer, date, date) does not exist instead. Define a real
datediff(text, date, date) yourself and you still get column "day" does
not exist. Writing your own DATEDIFF does not make
DATEDIFF(day, ...) work.
Which SQL date problems do these guides answer?
Five spellings from other dialects do not exist in PostgreSQL at all: DATEDIFF,
DATEADD, GETDATE, DATE_FORMAT and
STR_TO_DATE. format() and convert() do exist here with
different signatures: format(text, VARIADIC "any") is a template filler and
convert(bytea, name, name) is a text-encoding converter.
Three more mistakes are worse than a missing function, because PostgreSQL runs them and hands
you a wrong answer with no error. Ask DATE_PART('hour', end - start) for the hours
between two timestamps two days and nine hours apart and you get 9, the hour field of the
interval, when the real gap is 57 hours. Ask to_char() for 'HH:MM' at
half past nine on 4 March and you get 09:03, because MM is the month
pattern and MI is minutes. Ask for the same thing on 4 July and you get
09:07, and on 4 November, 09:11. Cast '03/04/2026' to
date under the default MDY DateStyle and you get 4 March, not
3 April.
- What replaces DATEDIFF in PostgreSQL?
Subtract the dates, and read why PostgreSQL answers DATEDIFF with a missing column. Hours, months and years too.
- What replaces DATEADD in PostgreSQL?
Add an integer or an INTERVAL, and see which one leaves you holding a timestamp.
- What is GETDATE() in PostgreSQL?
now() and current_date, why now() stands still for a whole transaction, and the filter that drops today’s rows.
- How do you convert a string to a date in PostgreSQL?
::date for ISO strings, to_date() for everything else, and the day-first date Postgres reads as a month.
- How do you format a date in PostgreSQL?
to_char() patterns with real output, the MM-for-minutes bug, and month labels that sort April first.
Where do dates fit in our free SQL course?
Nine of the sixty-six concepts, from the four date types through to sessionizing event data. Open any of them and you get the lesson, one practice problem with an editor in the page, and the task line for every other problem in that concept. Free, with no account needed to start.
- Date and Time Types in PostgreSQL
Choose between DATE, TIME, TIMESTAMP and TIMESTAMPTZ, and know what PostgreSQL keeps in each.
- Date Truncation and Extraction
Round a timestamp down to a day, week or month with DATE_TRUNC, and pull a single field out with EXTRACT.
- Date Arithmetic and Intervals
Add and subtract dates, and read the type you get back when an INTERVAL is in the expression.
- generate_series() for Sequences and Date Spines
Generate one row per day, week or month, including the periods you have no data for.
- Date Spine Construction and Zero-Fill Patterns
LEFT JOIN your facts onto a complete run of dates, so you get a zero for a quiet week instead of no row at all.
- Period-over-Period Analysis
Compare this week against last week, and this month against the same month a year ago.
- Grouping by Date Periods
Group by a truncated date for one row per period, and label it without breaking the sort.
- Running Totals and Cumulative Metrics
Carry a running total forward across ordered rows with a window function.
- Sessionization and Funnel Analysis Patterns
Cut a stream of timestamped events into sessions on the gaps, then measure who made it through each step.
