Snowflake Subtract Minutes from Timestamp: How-To

To subtract minutes from a timestamp in Snowflake, use the DATEADD function with a negative number: DATEADD(minute, -30, your_timestamp). This returns a timestamp that is 30 minutes earlier, and it works with all timestamp data types (TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ).

Formula

The underlying calculation treats a timestamp as a number of days since the epoch (1970-01-01). To subtract m minutes, you subtract m/1440 days (since 1 day = 1440 minutes).

Formula: new_timestamp = timestamp - (minutes / 1440)

Where:

• timestamp – the starting timestamp (e.g., 2024-01-15 12:30:00)
• minutes – the number of minutes to subtract (e.g., 30)
• new_timestamp – the resulting timestamp (e.g., 2024-01-15 12:00:00)

In SQL, you would write DATEADD(minute, -30, '2024-01-15 12:30:00'::TIMESTAMP).

Worked Example

Using the default values from the calculator: timestamp = 2024-01-15 12:30:00, minutes = 30.

1. Convert 30 minutes to days: 30 / 1440 = 0.0208333 days.
2. Subtract that from the timestamp’s internal day count. Since 12:30 is 0.520833 days into the day, the result is 0.520833 – 0.020833 = 0.500000 days, which is 12:00:00.
3. The new timestamp is 2024-01-15 12:00:00.

The calculator shows the same result: 2024-01-15 12:00:00.

Using DATEADD in Snowflake

Snowflake’s DATEADD function is the standard way to add or subtract time intervals. The syntax is:

DATEADD( <date_or_time_part>, <value>, <date_or_time_expr> )

For subtracting minutes, set the value to a negative number. For example:

• DATEADD(minute, -15, '2024-03-01 09:00:00'::TIMESTAMP) → 2024-03-01 08:45:00
• DATEADD(minute, -90, '2024-03-01 09:00:00'::TIMESTAMP) → 2024-03-01 07:30:00
• DATEADD(minute, -1440, '2024-03-01 09:00:00'::TIMESTAMP) → 2024-02-29 09:00:00 (leap year handled automatically)

You can also use TIMESTAMPADD with the same syntax, but DATEADD is more common in Snowflake.

Timestamp Types and Time Zones

Snowflake supports three timestamp types:

• TIMESTAMP_NTZ – no time zone, treats the value as a local wall-clock time.
• TIMESTAMP_LTZ – uses the session time zone for display, but stores UTC internally.
• TIMESTAMP_TZ – stores both the timestamp and the time zone offset.

When you subtract minutes, the operation is performed on the underlying UTC value for LTZ and TZ. For example, if your session time zone is America/New_York and you subtract 30 minutes from 2024-01-15 12:30:00 -05:00, the result is 2024-01-15 12:00:00 -05:00 (the offset stays the same). If you use a TIMESTAMP_NTZ, it simply subtracts the minutes without any time zone conversion.

Common Mistakes and Edge Cases

One frequent error is mixing up the order of arguments. The value comes before the timestamp, so DATEADD(minute, -30, timestamp) is correct, not DATEADD(timestamp, -30, minute). Another mistake is using a positive number when you meant to subtract – always use a negative value.

Edge cases to watch:

• Subtracting enough minutes to cross a day boundary: e.g., subtracting 30 minutes from 2024-01-01 00:10:00 gives 2023-12-31 23:40:00. Snowflake handles this automatically.
• Leap years and daylight saving time: Snowflake’s DATEADD works on the internal UTC representation, so DST transitions are handled correctly for LTZ and TZ types. For NTZ, it just does arithmetic on the wall-clock time.
• Fractional minutes: DATEADD accepts fractional values (e.g., -0.5 for 30 seconds), but it’s clearer to use second for sub-minute precision.

Alternatives to DATEADD

If you need to subtract minutes from a timestamp and want a more readable expression, you can use interval arithmetic:

SELECT '2024-01-15 12:30:00'::TIMESTAMP - INTERVAL '30 minutes';
-- Returns 2024-01-15 12:00:00

This is equivalent to DATEADD and is often preferred in queries for clarity. You can also use TIMESTAMPADD, which is a synonym for DATEADD in Snowflake.

For more complex calculations, such as subtracting minutes from a column in a table, you can use:

SELECT event_time, DATEADD(minute, -30, event_time) AS start_time
FROM events;

Performance and Best Practices

DATEADD is a built-in function, so it performs well even on large datasets. There is no need to write custom arithmetic. To make queries more efficient, consider:

• Using the correct timestamp type for your use case (NTZ for local times, LTZ for global apps).
• Avoiding wrapping timestamp columns in functions in WHERE clauses, as that can prevent partition pruning. Instead, compute the target timestamp and compare directly.
• Using DATEADD in SELECT lists is fine; it’s only in predicates that you should be careful.

Frequently Asked Questions

How do I subtract 1 hour from a timestamp in Snowflake?

Use DATEADD(hour, -1, your_timestamp) or DATEADD(minute, -60, your_timestamp). Both work, but using the hour part is more readable.

What if I need to subtract minutes from a string that is not a timestamp?

First convert the string to a timestamp using TO_TIMESTAMP() or ::TIMESTAMP. For example: DATEADD(minute, -30, '2024-01-15 12:30:00'::TIMESTAMP).

Does DATEADD work with TIMESTAMP_TZ?

Yes. It subtracts the minutes from the UTC value and preserves the original time zone offset. The result is still a TIMESTAMP_TZ.

Can I subtract minutes from a date (not a timestamp)?

Yes, but the result will be a timestamp. For example, DATEADD(minute, -30, '2024-01-15'::DATE) returns 2024-01-14 23:30:00.

What is the difference between DATEADD and TIMESTAMPADD?

None. They are synonyms in Snowflake. Both accept the same arguments and return the same results.

How do I subtract minutes from the current time?

Use DATEADD(minute, -30, CURRENT_TIMESTAMP()). This works with any of the current timestamp functions like SYSDATE() or GETDATE().

Related

• Bacl2 Molarity Calculation
• Car Cleaning Kit for Road Trips: What to Pack
• CCM to Liters Per Minute Conversion Guide

in t | Timestamp | - | 2024-01-15 12:30:00 | 0
in m | Minutes to subtract | min | 30 | 0
out res | New timestamp | - | t - (m / 1440) | 0