Manipulating datetime data

1. Manipulating datetime data

Text data. Done. Numeric data. Done. Let's learn how to manipulate datetime data!

2. Extracting timestamp components

There are times when we don't want to work with an entire timestamp; we may only want to work with a value's year, or maybe the hour. Luckily, Snowflake provides these functions on the right, and more to extract components from a `DATE`, `TIME`, or `TIMESTAMP`. If we'd like to extract the `DAY` from a value, we can pass it to the `DAY` function. Let's take a look at some more examples!

3. Parsing timestamps

We'd like to extract different components of the `exam_completion_time`, including the year, month and day name, day, hour, minute, and second. Using the syntax from before, we can do this!

4. Parsing timestamps

Our results look like this; let's dig in a bit more. For the first record shown, we've successfully extracted the year (2025), month name (Jun), and hour (11) from the `exam_completed_time`, along with a handful of other values. This is quite powerful when trying to understand general patterns, such as the time of day students complete an exam.

5. DATEDIFF

If we'd like to find the interval between two timestamps, we can use the `DATEDIFF` function. `DATEDIFF` works with the `DATE`, `TIME`, and `TIMESTAMP` data types, similar to other datetime functions we've used. `DATEDIFF` takes three values; the unit of time the result will be displayed in, the starting timestamp, and the ending timestamp, in that order. This unit of time could be something like `MINUTE`, `HOUR`, `DAY`, `YEAR`, or `WEEK` - we'll see an example in a bit.

6. Using DATEDIFF

For example, if the starting timestamp is May 12th at 8:24 and the ending timestamp is November 13 at 3:05, the interval is 185 days.

7. DATEADD

We can also add intervals of time to `DATE`, `TIME`, and `TIMESTAMP` values using the `DATEADD` function. `DATEADD` takes 3 fields. The first is the unit of time to add, such as `MINUTE`, `HOUR`, `DAY`, `YEAR`, etc. The second is the number of units to add to the third field, which is the date or timestamp to increment. Here, we're adding 185 DAYs to May 12, which spits out November 13. Let's take a look at another example!

8. Manipulate a DATE

We'd like to see how long before the due date students completed an exam. To do this, we can use the `DATEDIFF` function. We'd like the return value to be in hours, so `HOUR` will be the first value we pass to `DATEDIFF`. Remember, after `HOUR`, the first timestamp passed to `DATEDIFF` should be smaller than the second. Here, that's the case with `exam_completed_time` and `exam_due_time`. Students aren't the only ones with deadlines; teachers must complete grading within 1 week of a student completing an exam. Using the `DATEADD` function, we can add 1 week to the `exam_completed_time` to find the time that a grade is due back to each student.

9. Manipulate a DATE

Here are the results! Student 919 submitted their exam 22 hours early. The teacher then must return the grade before July 2 at 11:17 AM. Student 248 has an interesting output; since they submitted their exam after the deadline, the value for `hours_early` is negative. This occurs when the first timestamp passed to `DATEDIFF` is larger than the second.

10. Let's practice!

You know the drill! It's time to practice with a couple of hands-on exercises!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.