make_date and make_time assemble a value out of ready-made numbers: you pass the year, month, and day (or hour, minute, second) as separate integers instead of parsing a string. They are the perfect tool when the parts of a date already sit in different report columns.
Basic syntax
make_date(year, month, day) returns a date, and make_time(hour, min, sec) returns a time. The arguments are integers (seconds are double precision so they can hold a fraction). There are no format strings like 'YYYY-MM-DD' and none of the locale surprises that come with them.
SELECT
make_date(2024, 3, 15) AS d,
make_time(14, 30, 0) AS t;
Seconds accept a fraction, so a millisecond-precise moment is built without any string casting:
SELECT make_time(9, 5, 30.5) AS t;
A full timestamp comes from make_timestamp(year, month, day, hour, min, sec), and the zoned version is make_timestamptz(...).
Building a date from separate columns
The main scenario is a table where the year, month, and day are stored as numbers in different fields. Suppose a load from a legacy source dropped the date parts into orders separately:
SELECT
order_id,
make_date(y, m, d) AS order_date
FROM order_parts;
make_date removes the pain of concatenation and CAST: you do not have to build the string y || '-' || m || '-' || d and hope that a single-digit month does not break the format. The numbers go into the function as they are.
The same trick is handy for filtering on a date built from parameters. All orders for a specific day, assembled from three numbers:
SELECT id, user_id, amount
FROM orders
WHERE created_at::date = make_date(2024, 3, 15)
AND status = 'paid';
Validation of out-of-range parts
make_date does not stay silent on nonsense: a value outside the valid range raises an error instead of rolling over into the next month the way interval arithmetic does.
SELECT make_date(2024, 13, 1);
SELECT make_date(2024, 2, 30);
That is both a feature and a trap:
- Feature: dirty data (month
13, day 0) is caught immediately instead of quietly turning into a wrong date.
- Gotcha: one broken row in a large scan kills the whole query. If the data is not clean, validate the parts up front.
Guard against bad rows with a pre-filter:
SELECT order_id, make_date(y, m, d) AS order_date
FROM order_parts
WHERE m BETWEEN 1 AND 12
AND d BETWEEN 1 AND 31;
A negative year is read as BC, so make_date(-1, 1, 1) is not an error but 0001-01-01 BC. A year of zero is rejected.
When make_time pays off
make_time shines when the hour and minute come from a config or a join. For example, normalize a department's "work window" to a single start time:
SELECT
e.dept,
COUNT(*) AS people,
make_time(9, 0, 0) AS shift_start
FROM employees e
GROUP BY e.dept;
The assembled time can be added to a date to get a full moment with no string parsing:
SELECT
u.id,
make_date(2024, 3, 15) + make_time(18, 0, 0) AS reminder_at
FROM users u
WHERE u.country = 'DE';
MySQL and ClickHouse differences
The names and behavior differ from PostgreSQL.
- MySQL has no
make_date in the usual sense. It has MAKEDATE(year, dayofyear), which expects a year and a day of year, not a month and day. To build from month and day you use STR_TO_DATE, and for time you use MAKETIME(hour, minute, second).
SELECT
STR_TO_DATE('2024-3-15', '%Y-%c-%e') AS d,
MAKETIME(14, 30, 0) AS t;
- ClickHouse offers
makeDate(year, month, day) and makeDateTime(year, month, day, hour, minute, second), whose names line up in meaning with PostgreSQL.
SELECT
makeDate(2024, 3, 15) AS d,
makeDateTime(2024, 3, 15, 14, 30, 0) AS t;
The key difference when porting: MySQL MAKEDATE takes a day of year, not a month and day, and will happily swallow input that PostgreSQL would reject. Always check the signature before moving a query between databases.
make_dateandmake_timeassemble a value out of ready-made numbers: you pass the year, month, and day (or hour, minute, second) as separate integers instead of parsing a string. They are the perfect tool when the parts of a date already sit in different report columns.Basic syntax
make_date(year, month, day)returns adate, andmake_time(hour, min, sec)returns atime. The arguments are integers (seconds aredouble precisionso they can hold a fraction). There are no format strings like'YYYY-MM-DD'and none of the locale surprises that come with them.SELECT make_date(2024, 3, 15) AS d, -- 2024-03-15 make_time(14, 30, 0) AS t; -- 14:30:00Seconds accept a fraction, so a millisecond-precise moment is built without any string casting:
SELECT make_time(9, 5, 30.5) AS t; -- 09:05:30.5A full timestamp comes from
make_timestamp(year, month, day, hour, min, sec), and the zoned version ismake_timestamptz(...).Building a date from separate columns
The main scenario is a table where the year, month, and day are stored as numbers in different fields. Suppose a load from a legacy source dropped the date parts into
ordersseparately:-- order_parts(order_id, y, m, d) SELECT order_id, make_date(y, m, d) AS order_date FROM order_parts;make_dateremoves the pain of concatenation andCAST: you do not have to build the stringy || '-' || m || '-' || dand hope that a single-digit month does not break the format. The numbers go into the function as they are.The same trick is handy for filtering on a date built from parameters. All orders for a specific day, assembled from three numbers:
SELECT id, user_id, amount FROM orders WHERE created_at::date = make_date(2024, 3, 15) AND status = 'paid';Validation of out-of-range parts
make_datedoes not stay silent on nonsense: a value outside the valid range raises an error instead of rolling over into the next month the way interval arithmetic does.SELECT make_date(2024, 13, 1); -- ERROR: date field value out of range SELECT make_date(2024, 2, 30); -- ERROR: date field value out of rangeThat is both a feature and a trap:
13, day0) is caught immediately instead of quietly turning into a wrong date.Guard against bad rows with a pre-filter:
SELECT order_id, make_date(y, m, d) AS order_date FROM order_parts WHERE m BETWEEN 1 AND 12 AND d BETWEEN 1 AND 31;A negative year is read as BC, so
make_date(-1, 1, 1)is not an error but0001-01-01 BC. A year of zero is rejected.When make_time pays off
make_timeshines when the hour and minute come from a config or a join. For example, normalize a department's "work window" to a single start time:SELECT e.dept, COUNT(*) AS people, make_time(9, 0, 0) AS shift_start FROM employees e GROUP BY e.dept;The assembled
timecan be added to a date to get a full moment with no string parsing:SELECT u.id, make_date(2024, 3, 15) + make_time(18, 0, 0) AS reminder_at FROM users u WHERE u.country = 'DE';MySQL and ClickHouse differences
The names and behavior differ from PostgreSQL.
make_datein the usual sense. It hasMAKEDATE(year, dayofyear), which expects a year and a day of year, not a month and day. To build from month and day you useSTR_TO_DATE, and for time you useMAKETIME(hour, minute, second).SELECT STR_TO_DATE('2024-3-15', '%Y-%c-%e') AS d, -- 2024-03-15 MAKETIME(14, 30, 0) AS t; -- 14:30:00makeDate(year, month, day)andmakeDateTime(year, month, day, hour, minute, second), whose names line up in meaning with PostgreSQL.SELECT makeDate(2024, 3, 15) AS d, makeDateTime(2024, 3, 15, 14, 30, 0) AS t;The key difference when porting: MySQL
MAKEDATEtakes a day of year, not a month and day, and will happily swallow input that PostgreSQL would reject. Always check the signature before moving a query between databases.