MAKE_TIMESTAMP

Construct a TIMESTAMP WITHOUT TIME ZONE from year, month, day, hour, minute, and second components.

Category: datetimeReturns: TIMESTAMPDialect: Standard

Syntax

MAKE_TIMESTAMP(year, month, day, hour, min, sec)

Description

## Overview Constructs a TIMESTAMP WITHOUT TIME ZONE from six integer/fraction components. Use MAKE_TIMESTAMP when individual date and time components are stored in separate columns and you need a single TIMESTAMP value. For zone-aware construction, use MAKE_TIMESTAMPTZ which accepts an optional time zone argument. ## Behavior - Returns a TIMESTAMP WITHOUT TIME ZONE. - Returns NULL if any argument is NULL. - Raises an error for invalid components: month outside 1-12, day outside the month's valid range, hour outside 0-23, minute outside 0-59, seconds outside 0 through 60. - Leap years are handled correctly: MAKE_TIMESTAMP(2024, 2, 29, ...) succeeds, MAKE_TIMESTAMP(2025, 2, 29, ...) raises an error. - Does not perform rollover (hour 24 or day 32 are errors, not silently normalized). ## Timezone handling - Returns TIMESTAMP WITHOUT TIME ZONE: the resulting value is naive and does not carry zone information. - For an instant-in-time with zone information, use MAKE_TIMESTAMPTZ. ## Compatibility - Matches the MAKE_TIMESTAMP convention used across analytical SQL engines.

Parameters

Name	Type	Description
`year`		Specifies the year component as an INTEGER (for example 2025).
`month`		Specifies the month component as an INTEGER in the range 1 through 12.
`day`		Specifies the day-of-month in the valid range for the given month.
`hour`		Specifies the hour component as an INTEGER in the range 0 through 23.
`min`		Specifies the minute component as an INTEGER in the range 0 through 59.
`sec`		Specifies the seconds component as a DOUBLE in the range 0.0 through 59.999..., including fractional seconds.

Examples

-- Construct a specific timestamp
SELECT MAKE_TIMESTAMP(2025, 3, 15, 14, 30, 0) AS ts;

-- New Year's Day at midnight
SELECT MAKE_TIMESTAMP(2025, 1, 1, 0, 0, 0) AS ts;

-- Timestamp with fractional seconds
SELECT MAKE_TIMESTAMP(2025, 6, 15, 9, 0, 30.5) AS ts;

-- Build period-start timestamps from a fiscal calendar
SELECT MAKE_TIMESTAMP(yr, mo, dy, 0, 0, 0) AS period_start
FROM finance.accounting.calendar_dim;

-- Leap day with a specific time
SELECT MAKE_TIMESTAMP(2024, 2, 29, 12, 0, 0) AS leap_noon;

-- Invalid input raises an error
SELECT MAKE_TIMESTAMP(2025, 13, 1, 0, 0, 0); -- month 13 rejected

Pitfalls

Invalid components raise errors rather than silently rolling over. MAKE_TIMESTAMP(2025, 13, 1, ...) errors rather than producing January 1 2026. Validate inputs or use computed arithmetic instead.
The result is TIMESTAMP WITHOUT TIME ZONE. Storing this in a TIMESTAMP WITH TIME ZONE column applies an implicit zone conversion using the session zone at write time.
Fractional seconds are preserved up to the engine's precision. For lossless round-trips, use a TIMESTAMP type with enough fractional-second precision.