Using Snowflake Timestamp: 3 Comprehensive Aspects

Data and time are 2 very important aspects of your data. Whether you are working with data in real-time or not, it is important to know when a particular record was generated, modified, etc.

Snowflake TIMESTAMP is a data type that lets you do just that. This data type combines date and time into a single entity and is thus very useful. 

Introduction to Snowflake

Snowflake is a modern-day Data Warehouse that provides an integrated Cloud-based solution to your business. It enables storage facility, computation power, and workgroup resources to change the scale as per requirements. Using Snowflake, you can avoid worrying about pre-planning the size and computation requirements of your work in advance. You can simply add more computing power and space either automatically or in a few clicks.

Moreover with Snowflake, you can consolidate a Data Warehouse and a Data Lake in a single system to support your data. Snowflake provides you with the flexibility to seamlessly expand as your data processing increases. It also provides the facility to load new data without affecting ongoing queries.

Key Features of Snowflake

Here are a few key features of Snowflake:

• Scalable: Snowflake offers a safe and scalable platform that can process an unlimited number of virtual Data Warehouses independent from each other.
• Pay Per Use Model: Snowflake offers its services using a pay per usage model.
• High Processing Speed: Snowflake’s Virtual Warehouses are based on MPP cluster (Massive Parallel Processing).
• Separate Storage and Compute Layer: Snowflake uses different storage and computes layers that can scale up or down without affecting the other.
• Disaster Recovery: Data is replicated three times (by default) across the availability zones and regions, hence providing a complete fail-safe and fault-tolerant system.

Introduction to Snowflake TIMESTAMP

Snowflake TIMESTAMP is a user-specified alias that is attributed to one of the TIMESTAMP_* variants. For every operation which uses the Snowflake TIMESTAMP, the associated TIMESTAMP_* variant is used automatically. This data type is never stored in tables. The TIMESTAMP_* variant associated with the TIMESTAMP data type is specified by the TIMESTAMP_TYPE_MAPPING session parameter. By default, the parameter is set to TIMESTAMP_NTZ.

Variations of Timestamp

Snowflake provides support for three variations of timestamps. Each one of the timestamp variations, including the TIMESTAMP alias, provides support for an optional precision parameter for fractional seconds, e.g. TIMESTAMP(5). This precision can lie in the range of 0 (seconds) to 9 (nanoseconds). The precision is set to 9 by default.

A list of all the Timestamp formats supported by this data type can be found here.

Let’s look at the different session parameters provided by Snowflake:

1. TIMESTAMP_LTZ

TIMESTAMP_LTZ will internally store the UTC time with determined precision. However, each one of the operations is performed in the time zone specified in the current session, controlled by the TIMEZONE session parameter.

The various aliases available for TIMESTAMP_LTZ are as follows::

• TIMESTAMPLTZ
• TIMESTAMP WITH LOCAL TIME ZONE

Example:

alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

2. TIMESTAMP_NTZ

TIMESTAMP_NTZ will internally store the “wallclock” time with determined precision. Each of the operations is performed without considering any time zone. When the output format contains a time zone, the UTC indicator (Z) will be displayed. It is also the default for TIMESTAMP.

The various aliases available for TIMESTAMP_NTZ are as follows::

• TIMESTAMPNTZ
• TIMESTAMP WITHOUT TIME ZONE

Example:

create or replace table ts_test(ts timestamp_ntz);
alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

3. TIMESTAMP_TZ

TIMESTAMP_TZ will internally store UTC time along with an linked time zone offset. If a time zone is not given, the session time zone offset will be employed. Each one of these operations is performed with the time zone offset attributed to each record.

The various aliases available for TIMESTAMP_TZ are as follows::

• TIMESTAMPTZ
• TIMESTAMP WITH TIME ZONE

create or replace table ts_test(ts timestamp_tz);
alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

TO_TIMESTAMP() Function

This function lets you convert an input expression into the specified timestamp mapping type:

• TO_TIMESTAMP_LTZ (timestamp with local time zone)
• TO_TIMESTAMP_NTZ (timestamp with no time zone)
• TO_TIMESTAMP_TZ (timestamp with time zone)

Syntax:

timestampFunction ( <numeric_expr> [ , <scale> ] )
timestampFunction ( <date_expr> )
timestampFunction ( <timestamp_expr> )
timestampFunction ( <string_expr> [ , <format> ] )
timestampFunction ( '<integer>' )
timestampFunction ( <variant_expr> )

For further information on the different arguments and when to use them, read the official documentation.

Example:

select to_timestamp_tz('2013-04-05 01:02:03');

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function gives you the current timestamp for the system. 

Syntax:

CURRENT_TIMESTAMP( [ <fract_sec_precision> ] )
CURRENT_TIMESTAMP

NOTE:

fract_sec_precision: It is an optional argument that indicates the precision with which you want to get the time. For example, a value of 2 would show 2 digits after the decimal point. By default, the precision is 9 (nanoseconds). The range of acceptable values lies from 0 – 9. However, there are many platforms that do not support true nanosecond precision; the precision that you get might be less than the precision you specify. Practically, the precision is usually approximately around milliseconds (3 digits) at most.

Converting Timestamps to Alternative Time Zones

The following example shows a set of timestamp values stored with no time zone data. These timestamps are then  loaded in UTC time and converted to other time zones:

alter session set timezone = 'UTC';
alter session set timestamp_ltz_output_format = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';
create or replace table utctime (ntz timestamp_ntz);
insert into utctime values ('2016-05-01 00:00:00.000');
select * from utctime;
select convert_timezone('UTC','America/Chicago', ntz)::timestamp_ltz as chicagotime
from utctime;
select convert_timezone('UTC','America/Los_Angeles', ntz)::timestamp_ltz as latime

Conclusion

You have now learned how to use the Snowflake TIMESTAMP data type, the different types of TIMESTAMP_TYPE_MAPPING, and the different functions associated with it. You can now easily use this to store, read or update data based on data and time parameters.

Visit our Website to Explore Hevo

The Snowflake Data Warehouse is a great tool for storing your surplus data. However, at times, you need to accumulate data from multiple sources to your Snowflake Data Warehouse for further analysis. Building an in-house solution for this process could be an expensive and time-consuming task Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Have any further queries? Get in touch with us in the comments section below.