Date Functions Compared Across Databases: PostgreSQL, MySQL, SQL Server, Oracle

1. Why date functions turn out so differently

Hardly any area of SQL differs as much between the major database systems as date functions. While SELECT, JOIN, and WHERE mostly work uniformly, practically every system has developed its own function names, its own argument order, and its own conventions for date arithmetic. The reason is that the ANSI SQL standard only incompletely covers date operations, and most databases implemented their own pragmatic solutions long before any standardization.

The result: anyone who wants to solve a simple task like "add 30 days to this date" in PostgreSQL, MySQL, SQL Server, and Oracle needs four different lines of code. This divergence in date functions is one of the most common reasons applications supporting multiple databases need their own abstraction layer for date logic. The following sections systematically show what the most important date functions look like in the four most common systems.

2. Querying the current date and current time

Even the simplest date function, querying the current point in time, differs in syntax between systems. PostgreSQL, MySQL, and Oracle support the standard function CURRENT_TIMESTAMP without parentheses, while SQL Server traditionally prefers GETDATE(), even though CURRENT_TIMESTAMP also works there as an alias. For the plain date without a time component, PostgreSQL offers CURRENT_DATE, MySQL also offers CURDATE() in addition to CURRENT_DATE, and Oracle uses SYSDATE, which unlike the other systems returns both date and time.

An important difference in Oracle: SYSDATE returns the server time without time zone information, while CURRENT_TIMESTAMP in Oracle respects the session time zone. Anyone working in a distributed application across multiple time zones should deliberately distinguish between these two functions in Oracle, since confusing them can lead to subtle, hard to reproduce bugs in time critical calculations.

-- PostgreSQL: standard functions, timezone-aware by default
SELECT CURRENT_TIMESTAMP, CURRENT_DATE, NOW();

-- MySQL: standard functions plus shorthand aliases
SELECT CURRENT_TIMESTAMP, CURDATE(), NOW();

-- SQL Server: GETDATE() is the traditional choice
SELECT GETDATE(), CAST(GETDATE() AS DATE), SYSDATETIME();

-- Oracle: SYSDATE has no timezone, CURRENT_TIMESTAMP respects session timezone
SELECT SYSDATE, CURRENT_TIMESTAMP FROM dual;

3. Date arithmetic: adding days, months, and years

Date arithmetic shows the greatest syntactic variety between database systems. PostgreSQL allows the elegant, standard leaning notation date + INTERVAL '30 days', which can be embedded directly in expressions. MySQL instead uses the function DATE_ADD(date, INTERVAL 30 DAY) with similar but not identical syntax. SQL Server uses the function DATEADD(day, 30, date) with the order of unit and value swapped compared to MySQL. Oracle finally allows both simple integer addition for days (date + 30) and INTERVAL literals for months and years.

These differences in date functions regularly cause bugs when developers switch between projects with different databases. Particularly tricky is the Oracle quirk that a plain date + 30 is interpreted as "add 30 days", while in PostgreSQL the same notation without an INTERVAL specification produces a type error, because PostgreSQL does not implicitly interpret integer addition on a date as day arithmetic.

-- PostgreSQL: INTERVAL literal, embeddable in expressions
SELECT order_date + INTERVAL '30 days' AS due_date FROM orders;
SELECT order_date + INTERVAL '3 months' AS renewal_date FROM orders;

-- MySQL: DATE_ADD function with INTERVAL keyword
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;
SELECT DATE_ADD(order_date, INTERVAL 3 MONTH) AS renewal_date FROM orders;

-- SQL Server: DATEADD with swapped unit/value order
SELECT DATEADD(day, 30, order_date) AS due_date FROM orders;
SELECT DATEADD(month, 3, order_date) AS renewal_date FROM orders;

-- Oracle: plain integer addition for days, INTERVAL for months/years
SELECT order_date + 30 AS due_date FROM orders;
SELECT ADD_MONTHS(order_date, 3) AS renewal_date FROM orders;

4. Calculating differences between two points in time

Calculating the difference between two timestamps is another task where date functions diverge considerably. PostgreSQL returns an INTERVAL result directly when subtracting two TIMESTAMP values, combining days, hours, and minutes. MySQL offers TIMESTAMPDIFF(unit, start, end), an explicit function that returns the difference in a chosen unit. SQL Server uses DATEDIFF(unit, start, end) with a similar signature but a different function name.

Oracle treats the subtraction of two DATE values as a plain floating point number of days, which is initially unfamiliar to developers from other systems but practical once you know the behavior. For differences in units other than days, Oracle additionally offers EXTRACT combined with interval subtraction on TIMESTAMP values. Anyone calculating date differences across databases should always explicitly document the intended target unit (days, hours, minutes) in the code, since the implicit return values vary considerably between systems.

-- PostgreSQL: subtraction returns an INTERVAL
SELECT shipped_at - ordered_at AS delivery_duration FROM orders;
SELECT EXTRACT(DAY FROM shipped_at - ordered_at) AS delivery_days FROM orders;

-- MySQL: explicit function with chosen unit
SELECT TIMESTAMPDIFF(DAY, ordered_at, shipped_at) AS delivery_days FROM orders;
SELECT TIMESTAMPDIFF(HOUR, ordered_at, shipped_at) AS delivery_hours FROM orders;

-- SQL Server: DATEDIFF with unit as first argument
SELECT DATEDIFF(day, ordered_at, shipped_at) AS delivery_days FROM orders;

-- Oracle: subtraction returns a plain number of days
SELECT shipped_at - ordered_at AS delivery_days FROM orders;

5. Extracting individual date parts

For extracting individual components of a date, such as year, month, weekday, or calendar week, PostgreSQL offers the standard leaning function EXTRACT(field FROM date), which is also supported by MySQL and Oracle. That makes EXTRACT one of the few genuinely portable date functions between systems. SQL Server has only fully supported EXTRACT in newer versions and traditionally uses the function DATEPART(unit, date), which is functionally equivalent but has different syntax.

For calendar weeks, additional subtleties appear: PostgreSQL follows the ISO 8601 definition by default, where the week starts on Monday and the first week of the year must contain at least four days in the new year. MySQL offers different modes via the second parameter of the function WEEK(date, mode), mapping different week definitions, and SQL Server's interpretation of the calendar week depends on the server setting DATEFIRST. These differences in date functions for calendar weeks are a common reason for diverging reporting numbers between systems.

-- PostgreSQL, MySQL, Oracle: EXTRACT works nearly identically
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(ISODOW FROM order_date) AS weekday
FROM orders;

-- SQL Server: DATEPART with unit as first argument
SELECT DATEPART(year, order_date) AS order_year,
       DATEPART(month, order_date) AS order_month,
       DATEPART(weekday, order_date) AS weekday
FROM orders;

6. Date formatting for output and comparison

Formatting date values for display is another area with heavily diverging date functions. PostgreSQL uses TO_CHAR(date, 'YYYY-MM-DD') with a format language that is very similar to Oracle, because both systems historically share related formatting conventions. MySQL offers DATE_FORMAT(date, '%Y-%m-%d') with a completely different format language based on percent sign placeholders instead of letter patterns. SQL Server uses FORMAT(date, 'yyyy-MM-dd') with .NET based format strings, which follow yet a third convention.

These three different format languages (Oracle style, MySQL style, .NET style) make formatting date values one of the least portable tasks in SQL. In practice, it is advisable to generally perform display formatting in the application layer and only retrieve ISO 8601 compliant TIMESTAMP values from the database, instead of scattering formatting logic across SQL queries that would need to be completely rewritten on a database switch.

-- PostgreSQL: Oracle-style format tokens
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date FROM orders;

-- MySQL: percent-sign placeholders
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;

-- SQL Server: .NET-style format string
SELECT FORMAT(order_date, 'yyyy-MM-dd') AS formatted_date FROM orders;

-- Oracle: same format tokens as PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date FROM orders;

7. Time zone handling: the biggest source of bugs

Time zone handling is the area of date functions that causes the most production bugs. PostgreSQL strictly distinguishes between TIMESTAMP (without time zone) and TIMESTAMPTZ (with time zone), where TIMESTAMPTZ is always stored internally in UTC and converted to the session time zone on output. This clear separation prevents many typical time zone bugs, but requires developers to deliberately choose the right type.

MySQL offers TIMESTAMP, a type also stored internally in UTC and converted to the session time zone on read and write access, while DATETIME performs no time zone conversion at all and stores the value exactly as entered. SQL Server has had the type DATETIMEOFFSET since version 2008, which explicitly stores the UTC offset, while DATETIME2 remains time zone unaware. Oracle offers two different variants with TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE, which differ in how the offset is stored. Anyone wanting to avoid time zone bugs should consistently use time zone aware types for all timestamps that carry meaning across system boundaries, such as order times in internationally operating shops.

8. Keeping date logic portable in the application

Given the substantial differences in date functions between major database systems, a deliberate architecture decision pays off: complex date arithmetic that goes beyond simple comparisons should either be handled entirely in the application layer (for example with a date library like Carbon in PHP or date-fns in JavaScript) or encapsulated behind a clearly defined database abstraction layer. Scattering raw SQL with database specific date functions directly through business logic makes any later migration considerably more expensive.

A pragmatic middle ground: simple comparisons and filters (WHERE order_date >= '2026-01-01') work nearly identically in all four systems and can safely remain in SQL. More complex calculations such as business day arithmetic, recurring appointments, or calendar week aggregations, on the other hand, benefit from a central implementation outside the database, where the logic can be tested once and applied consistently across all target databases.

9. Date functions side by side

The following table compares the most important date functions of the four systems for typical tasks.

This comparison shows why a 1 to 1 translation of date logic between systems without tests is risky. Small differences in rounding behavior, week definition, or time zone conversion often only surface at edge cases, such as month boundaries, leap years, or the switch between daylight saving and standard time.

10. Summary

Date functions are among the areas of SQL where database systems agree with each other the least. From querying the current point in time through date arithmetic and formatting to time zone handling, every system uses its own function names, its own argument order, and sometimes even different implicit behavior for an identical task. Knowing these differences helps avoid the most common sources of bugs during migrations and in applications supporting multiple databases.

The most important practical consequence: date logic that goes beyond simple comparisons ideally belongs in a central application layer rather than directly in database specific SQL. That keeps the most complex and error prone logic testable in one place, while the database itself only handles simple, largely portable date comparisons.

11. FAQ: Date Functions Compared Across Databases