Using Snowflake Timestamp: 3 Comprehensive Aspects

on Data Warehouse, Data Warehouses, Snowflake, Tutorials • July 30th, 2020 • Write for Hevo

Using Snowflake Timestamp: Easy to Follow Guide | Snowflake Timestamp | Cover

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. 

Table of Contents

Introduction to Snowflake

Snowflake Logo
Image Source

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. Moreover, you can execute tasks in parallel without stressing about memory management.
  • Pay Per Use Model: Snowflake offers its services using a pay per usage model. This implies, you only pay for the time that you use Snowflake. Shutting down the Data Warehouses after your work can save costs.
  • High Processing Speed: Snowflake’s Virtual Warehouses are based on MPP cluster (Massive Parallel Processing). They allow you to execute processes in parallel without degrading other clusters’ performance.
  • Separate Storage and Compute Layer: Snowflake uses different storage and computes layers that can scale up or down without affecting the other.
  • Disaster Recovery: In Snowflake, data is replicated three times (by default) across the availability zones and regions, hence providing a complete fail-safe and fault-tolerant system.

To learn more about Snowflake, visit here.

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.

Hevo Data, an Alternative you Can Depend on to Load Data Efficiently

Hevo Data, a No-Code Data Pipeline helps you transfer data from multiple sources to Snowflake. Hevo is fully-managed and completely automates the process of not only exporting data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Check out some amazing features of Hevo (Official Snowflake ETL Partner):

  • Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export. 
  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of Schema management & automatically detects the Schema of incoming data and maps it to the destination Schema.
  • Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.

Get started with Hevo today! Sign up here for a 14-day free trial!

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 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 100+ 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.

No-Code Data Pipeline for Snowflake