Datetime data types

1. Datetime data types

We've learned to store text and numeric data. Next up - datetime data!

2. DATE data types

In Snowflake, "dates" store the day on which something occurred. For example, a date could store an individual's birthday, or the day that an item was purchased. The typical format for `DATE` values is "year-year-year-year, month-month, day-day". There are other formats, but for now, we'll stick with this. In Snowflake, the `DATE` keyword can be used define a new column, like this.

3. DATE data types

To convert a string into date, we'll use the `TO_DATE` function. A string can also be "casted" to a `DATE` using two colons. The syntax for this is string-colon-colon-`DATE`. Here's an example of what these `DATE`s looks like in a query output.

4. Time data types

Time data is quite similar to date data; it captures the hours, minutes, and seconds that some event occurred. For now, we'll work with time data in the format "hour-hour-minute-minute-second-second". The `TIME` keyword can be used to define a column of type `TIME`.

5. Time data types

We can also convert a string to a `TIME` value using the syntax `TO_TIME` function or the syntax string-colon-colon-`TIME`. See, `TIME` feels just like `DATE`!

6. Timestamp data types

What if we want to combine date and time data into a single value? We can do exactly this with `TIMESTAMP`. A `TIMESTAMP` will typically take the form `year-month-day hour-minute-second` with a space between. We'll use the `TIMESTAMP` keyword to define a column.

7. Timestamp data types

Just like with `DATE` and `TIME`, we can convert text using `TO_TIMESTAMP` or with two colons. `TIMESTAMP` is the most common way to store datetime data in Snowflake. We can even extract both the date and time from `TIMESTAMP`s.

8. Examples

To convert a string to a `DATE`, `TIME`, or `TIMESTAMP`, we'll use their respective function. For each data type, the syntax looks like this. To extract just the date from the transaction timestamp, we'll use the syntax `transactions_timestamp`-colon-colon-`DATE`. We could do the same thing to pull out the `TIME` instead. Here are the results!

9. Timezones

Timezones are tricky, regardless of the tool! Luckily, Snowflake helps to make timezones intuitive. By default, `DATE`, `TIME`, and `TIMESTAMP` data is stored without a timezone. However, there are three additional timestamp data types in Snowflake. `TIMESTAMP_NTZ` is the default implementation of `TIMESTAMP`. It stores "wallclock" time, without a timezone. You can think of NTZ as "no timezone". `TIMESTAMP_LTZ` leverages the local timezone when performing operations on that data. LTZ means "local timezone" `TIMESTAMP_TZ` requires an explicit timezone to be specified. This allows a Snowflake user to have complete control over the timezone that is stored in a column with this type.

10. Let's practice!

Beautiful! Now, it's TIME to practice working with TIME. Oh yeah, and DATEs.

Create Your Free Account

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