Snowflake SQL Tutorial 76 - TIMESTAMP_NTZ vs LTZ vs TZ, CONVERT_TIMEZONE - Dates 5

Snowflake SQL · Episode 76

In this post we are going to cover Snowflake's three timestamp types — TIMESTAMP_NTZ, TIMESTAMP_LTZ and TIMESTAMP_TZ — and the CONVERT_TIMEZONE function.

The three types all hold a date and a time, and they differ in what they know about time zones. TIMESTAMP_NTZ is a wall clock with no zone at all. TIMESTAMP_LTZ holds an instant and shows it in your session's zone. TIMESTAMP_TZ holds an instant and remembers the offset it arrived with.

We will start with the two session settings that decide what you see, and build it up step by step. On the way we will find one cast that moves your data by five and a half hours without an error or a warning, measure how long a day is across a daylight-saving boundary, and count how many real order dates change date when you read them in another zone.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 76 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's look at the two settings first
  2. Let's cast one piece of text to all three types
  3. Now let's put an offset on the text
  4. Let's prove that in epoch seconds
  5. Why does an NTZ equal an LTZ with the same digits?
  6. Now let's move the session zone and watch what changes
  7. Now let's convert on purpose with CONVERT_TIMEZONE
  8. Is the conversion lossless?
  9. Now the useful one: one instant, five offices
  10. What happens when the zone name is wrong?
  11. And if you do want a fixed offset?
  12. Now let's measure daylight saving instead of describing it
  13. There is also an hour that does not exist
  14. Now let's try it on real data
  15. One value end to end
  16. One last thing: put the session back

Let's look at the two settings first

Before any of this makes sense we need to know two things about the session we are in. Both of them change what the grids on this page look like, so your own numbers will differ from mine unless you set them the same way.

Sample data and session settings
-- ORDERS comes from the setup script this course uses throughout.
-- Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- Nothing on this page depends on the clock. Every timestamp below is written
-- out as a literal, so you get the same answers whenever you run it.
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ AS ANCHOR;

-- Two session settings decide what you see. We look at both of them in the
-- first section, and the post changes one of them on purpose later on and
-- puts it back at the end.
SHOW PARAMETERS LIKE 'TIMEZONE' IN SESSION;
SHOW PARAMETERS LIKE 'TIMESTAMP_TYPE_MAPPING' IN SESSION;

The first setting is the session time zone.

Query
SHOW PARAMETERS LIKE 'TIMEZONE' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
TIMEZONEAmerica/Los_AngelesAmerica/Los_Angelestime zoneSTRING

This session thinks it is in America/Los_Angeles, and that is also the default here. Yours will very likely say something else, and every offset you see in the next few grids will be different because of it.

The second setting decides what the plain word TIMESTAMP means.

Query
SHOW PARAMETERS LIKE 'TIMESTAMP_TYPE_MAPPING' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
TIMESTAMP_TYPE_MAPPINGTIMESTAMP_NTZTIMESTAMP_NTZIf TIMESTAMP type is used, what specific TIMESTAMP* type it should map to: TIMESTAMP_LTZ, TIMESTAMP_NTZ (default) or TIMESTAMP_TZSTRING

It says TIMESTAMP_NTZ. So when I write ::TIMESTAMP, that is the type I get.

Let's prove that rather than take it on trust. Four casts, and TYPEOF on each of them.

Query
SELECT TYPEOF('2024-06-30 14:37:52'::TIMESTAMP::VARIANT)     AS PLAIN_TIMESTAMP,
       TYPEOF('2024-06-30 14:37:52'::TIMESTAMP_NTZ::VARIANT) AS NTZ,
       TYPEOF('2024-06-30 14:37:52'::TIMESTAMP_LTZ::VARIANT) AS LTZ,
       TYPEOF('2024-06-30 14:37:52'::TIMESTAMP_TZ::VARIANT)  AS TZ;
Result1 row · 4 columns
PLAIN_TIMESTAMPNTZLTZTZ
TIMESTAMP_NTZTIMESTAMP_NTZTIMESTAMP_LTZTIMESTAMP_TZ

Plain TIMESTAMP came back as TIMESTAMP_NTZ, and the three named types each came back as themselves.

So plain TIMESTAMP is not a fourth type. It is an alias for whichever type that setting points at.

Let's cast one piece of text to all three types

Now the types themselves. Let's take one piece of text with no zone information in it at all, and cast it three ways in a single query.

Query
SELECT '2024-06-30 14:37:52'                         AS THE_TEXT,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ          AS AS_NTZ,
       '2024-06-30 14:37:52'::TIMESTAMP_LTZ          AS AS_LTZ,
       '2024-06-30 14:37:52'::TIMESTAMP_TZ           AS AS_TZ;
Result1 row · 4 columns
THE_TEXTAS_NTZAS_LTZAS_TZ
2024-06-30 14:37:522024-06-30 14:37:522024-06-30 14:37:52-07:002024-06-30 14:37:52-07:00

TIMESTAMP_NTZ gave the digits back unchanged and nothing else. No offset, no zone. It is a wall clock.

TIMESTAMP_LTZ gave the same digits with -07:00 attached, which is this session's offset. TIMESTAMP_TZ did the same thing.

Both of the zone-aware types had to assume a zone, because the text did not carry one, and the zone they assumed is the session's.

Now let's put an offset on the text

That is the easy case. Let's write the offset into the text and run the same three casts again.

Query
SELECT '2024-06-30 14:37:52 +05:30'                  AS THE_TEXT,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ    AS AS_TZ,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_LTZ   AS AS_LTZ,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_NTZ   AS AS_NTZ;
Result1 row · 4 columns
THE_TEXTAS_TZAS_LTZAS_NTZ
2024-06-30 14:37:52 +05:302024-06-30 14:37:52+05:302024-06-30 02:07:52-07:002024-06-30 14:37:52

TIMESTAMP_TZ kept it. 14:37:52 with +05:30 on it, exactly what we wrote.

TIMESTAMP_LTZ shows 02:07:52 with -07:00. Different digits, same moment, read on this session's clock.

Now look at the last column carefully, because it is the most important thing in this post. TIMESTAMP_NTZ gave 2024-06-30 14:37:52.

It kept the digits and dropped the offset. It did not convert anything. Those digits were half past two in the afternoon in India, and now they are half past two in nowhere.

Let's prove that in epoch seconds

I am not going to argue that from the display, because reading offsets off a screen is exactly how people talk themselves into the wrong answer. Let's use epoch seconds instead. An epoch second is one number counted from one fixed point, so it cannot be ambiguous.

First, the three casts side by side, so we know what we are measuring.

Query
SELECT '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ                     AS ORIGINAL_TZ,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_NTZ      AS TZ_TO_NTZ,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_LTZ      AS TZ_TO_LTZ,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ::TIMESTAMP_LTZ            AS NTZ_TO_LTZ;
Result1 row · 4 columns
ORIGINAL_TZTZ_TO_NTZTZ_TO_LTZNTZ_TO_LTZ
2024-06-30 14:37:52+05:302024-06-30 14:37:522024-06-30 02:07:52-07:002024-06-30 14:37:52-07:00

Now the same four expressions, through DATE_PART('epoch_second', ...).

Query
SELECT DATE_PART('epoch_second', '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ) AS TZ_EPOCH,
       DATE_PART('epoch_second',
         '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_NTZ)          AS TZ_TO_NTZ_EPOCH,
       DATE_PART('epoch_second',
         '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_LTZ)          AS TZ_TO_LTZ_EPOCH,
       DATE_PART('epoch_second', '2024-06-30 14:37:52'::TIMESTAMP_NTZ)       AS NTZ_EPOCH;
Result1 row · 4 columns
TZ_EPOCHTZ_TO_NTZ_EPOCHTZ_TO_LTZ_EPOCHNTZ_EPOCH
1719738472171975827217197384721719758272

The original TIMESTAMP_TZ value is 1719738472. Cast to TIMESTAMP_NTZ, the same value is 1719758272.

Those two numbers are 19,800 seconds apart, which is five and a half hours. That is exactly the offset that got thrown away.

Now look at the third column. Cast to TIMESTAMP_LTZ instead and the epoch is identical to the original. The moment survived.

This is the quiet one

Same syntax, no warning, no error, and one of the two casts moved the actual instant. If you have a TIMESTAMP_TZ and you want a different type, cast it to TIMESTAMP_LTZ. Casting it to TIMESTAMP_NTZ keeps the digits and loses the moment.

Why does an NTZ equal an LTZ with the same digits?

Here is a related thing that is easy to get comfortable with and should not be. Let's compare an NTZ and an LTZ built from the same text.

Query
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ AS NTZ_,
       '2024-06-30 14:37:52'::TIMESTAMP_LTZ AS LTZ_,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ = '2024-06-30 14:37:52'::TIMESTAMP_LTZ
         AS ARE_THEY_EQUAL,
       DATEDIFF('second', '2024-06-30 14:37:52'::TIMESTAMP_NTZ,
                          '2024-06-30 14:37:52'::TIMESTAMP_LTZ) AS SECONDS_APART;
Result1 row · 4 columns
NTZ_LTZ_ARE_THEY_EQUALSECONDS_APART
2024-06-30 14:37:522024-06-30 14:37:52-07:00TRUE0

They compare equal, and DATEDIFF says they are 0 seconds apart, even though one of them has an offset printed on it and the other does not.

That is because the comparison reads the NTZ in the session zone. Which is convenient right up to the point where the session zone is not the zone the data came from.

Now let's move the session zone and watch what changes

So what does the session zone actually control? Let's move it and look, rather than describe it.

Query
ALTER SESSION SET TIMEZONE = 'UTC';
Result1 row · 1 column
status
Statement executed successfully.

That ran. Now the same four expressions as before, with CURRENT_SESSION() on the grid as well so you can see this is one session and not a reconnect.

Query
SELECT CURRENT_SESSION()                                  AS SESSION_,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ               AS NTZ_,
       '2024-06-30 14:37:52'::TIMESTAMP_LTZ               AS LTZ_,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ         AS TZ_,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_LTZ AS TZ_SHOWN_AS_LTZ;
Result1 row · 5 columns
SESSION_NTZ_LTZ_TZ_TZ_SHOWN_AS_LTZ
47613577947874302024-06-30 14:37:522024-06-30 14:37:52+00:002024-06-30 14:37:52+05:302024-06-30 09:07:52+00:00

NTZ is unchanged. It has no zone, so there is nothing for the session to convert.

LTZ still reads 14:37:52, but the offset is now +00:00. Same instant, different clock.

TZ is unchanged and still carries +05:30, because it remembers its own offset and the session cannot touch it. And the last column, the same TZ value shown as an LTZ, reads 09:07:52 in UTC.

Let's move it once more, to somewhere with a different offset again.

Query
ALTER SESSION SET TIMEZONE = 'America/New_York';
Result1 row · 1 column
status
Statement executed successfully.

And the same four expressions a third time.

Query
SELECT CURRENT_SESSION()                                  AS SESSION_,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ               AS NTZ_,
       '2024-06-30 14:37:52'::TIMESTAMP_LTZ               AS LTZ_,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ         AS TZ_,
       '2024-06-30 14:37:52 +05:30'::TIMESTAMP_TZ::TIMESTAMP_LTZ AS TZ_SHOWN_AS_LTZ;
Result1 row · 5 columns
SESSION_NTZ_LTZ_TZ_TZ_SHOWN_AS_LTZ
47613577947874302024-06-30 14:37:522024-06-30 14:37:52-04:002024-06-30 14:37:52+05:302024-06-30 05:07:52-04:00

Same session id. NTZ unchanged again. LTZ now carries -04:00. TZ still +05:30. And the last column reads 05:07:52, which is the same instant for the third time.

So the session time zone is a display setting for LTZ, and that is all it is. It changes what you read, not what an NTZ or a TZ gives back.

Which means two people can run the same query, see different times, and both be right. Let's set it to UTC for the rest of the post so your numbers match mine.

Query
ALTER SESSION SET TIMEZONE = 'UTC';
Result1 row · 1 column
status
Statement executed successfully.

Every grid from here on was captured with the session time zone set to UTC. We put it back at the end of the post.

Now let's convert on purpose with CONVERT_TIMEZONE

CONVERT_TIMEZONE has two shapes, and that is what trips people up. Let's run both in one query so the difference is in front of us.

Query
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ                                      AS INPUT_,
       CONVERT_TIMEZONE('America/New_York', '2024-06-30 14:37:52'::TIMESTAMP_LTZ) AS TWO_ARG_ON_LTZ,
       CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-06-30 14:37:52'::TIMESTAMP_NTZ)
         AS THREE_ARG_UTC_TO_NY,
       CONVERT_TIMEZONE('America/New_York', 'UTC', '2024-06-30 14:37:52'::TIMESTAMP_NTZ)
         AS THREE_ARG_NY_TO_UTC;
Result1 row · 4 columns
INPUT_TWO_ARG_ON_LTZTHREE_ARG_UTC_TO_NYTHREE_ARG_NY_TO_UTC
2024-06-30 14:37:522024-06-30 10:37:52-04:002024-06-30 10:37:522024-06-30 18:37:52

With two arguments you give it a target zone and a value that already knows where it is. Our LTZ at 14:37:52 in UTC came back as 10:37:52 in New York.

With three arguments you give it a source zone, a target zone and a value. You need that form for an NTZ, because an NTZ cannot tell you where it is.

UTC to New York gives 10:37:52. The other way round, New York to UTC, gives 18:37:52. Same digits going in, and the direction is entirely up to you to get right.

The types coming back are different too, and that is worth knowing before it surprises you.

Query
SELECT TYPEOF(CONVERT_TIMEZONE('America/New_York',
         '2024-06-30 14:37:52'::TIMESTAMP_LTZ)::VARIANT) AS TWO_ARG_TYPE,
       TYPEOF(CONVERT_TIMEZONE('UTC', 'America/New_York',
         '2024-06-30 14:37:52'::TIMESTAMP_NTZ)::VARIANT) AS THREE_ARG_TYPE;
Result1 row · 2 columns
TWO_ARG_TYPETHREE_ARG_TYPE
TIMESTAMP_TZTIMESTAMP_NTZ

The two-argument form returns a TIMESTAMP_TZ. The three-argument form returns a TIMESTAMP_NTZ. Wall clock in, wall clock out.

Is the conversion lossless?

Fair question, and we can answer it rather than assume it. Let's go from UTC to Tokyo and straight back again, and compare the result with where we started.

Query
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ AS ORIGINAL,
       CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS IN_TOKYO,
       CONVERT_TIMEZONE('Asia/Tokyo', 'UTC',
         CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', '2024-06-30 14:37:52'::TIMESTAMP_NTZ))
         AS BACK_AGAIN,
       '2024-06-30 14:37:52'::TIMESTAMP_NTZ =
         CONVERT_TIMEZONE('Asia/Tokyo', 'UTC',
           CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', '2024-06-30 14:37:52'::TIMESTAMP_NTZ))
         AS SURVIVED;
Result1 row · 4 columns
ORIGINALIN_TOKYOBACK_AGAINSURVIVED
2024-06-30 14:37:522024-06-30 23:37:522024-06-30 14:37:52TRUE

23:37:52 in Tokyo, and back to 14:37:52. The comparison column says TRUE.

Now the useful one: one instant, five offices

This is the query you will actually write. One instant, read in five different places.

Query
SELECT CONVERT_TIMEZONE('UTC', 'UTC',               '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS UTC_,
       CONVERT_TIMEZONE('UTC', 'America/New_York',  '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS NEW_YORK,
       CONVERT_TIMEZONE('UTC', 'Europe/London',     '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS LONDON,
       CONVERT_TIMEZONE('UTC', 'Asia/Kolkata',      '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS KOLKATA,
       CONVERT_TIMEZONE('UTC', 'Australia/Sydney',  '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS SYDNEY;
Result1 row · 5 columns
UTC_NEW_YORKLONDONKOLKATASYDNEY
2024-06-30 14:37:522024-06-30 10:37:522024-06-30 15:37:522024-06-30 20:07:522024-07-01 00:37:52

Five columns, one moment. New York, London and Kolkata are all the same day at different times.

Now look at the Sydney column. It says 2024-07-01. That is the next day.

That last column is the whole reason time zones matter. One instant, and the date is different depending on where you read it.

What happens when the zone name is wrong?

Two things will catch you here, and the first one is straightforward. Let's hand it a zone that does not exist.

Query
SELECT CONVERT_TIMEZONE('UTC', 'Middle/Earth', '2024-06-30 14:37:52'::TIMESTAMP_NTZ)
         AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100094 (22000): Unknown timezone: 'Middle/Earth'

A clear error that names the value. That is a good error.

Here is the second one, and it surprised me. Let's hand it a plain offset instead of a zone name.

Query
SELECT CONVERT_TIMEZONE('UTC', '-05:00', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100094 (22000): Unknown timezone: '-05:00'

Same error. An offset is not a zone name, as far as CONVERT_TIMEZONE is concerned.

And yet the very same offset is perfectly fine inside a literal. Let's show that, because otherwise the rule looks arbitrary.

Query
SELECT '2024-01-15 12:00:00 -05:00'::TIMESTAMP_TZ                  AS PARSES_FINE,
       DATE_PART('epoch_second', '2024-01-15 12:00:00 -05:00'::TIMESTAMP_TZ)
         AS ITS_INSTANT,
       '2024-01-15 12:00:00 +05:30'::TIMESTAMP_TZ                  AS AND_THIS_ONE;
Result1 row · 3 columns
PARSES_FINEITS_INSTANTAND_THIS_ONE
2024-01-15 12:00:00-05:0017053380002024-01-15 12:00:00+05:30

That parses, and it has a real instant behind it. So an offset is legal in one place and illegal in the other. The literal is describing a moment. CONVERT_TIMEZONE wants a zone, and a zone is a set of rules over time, not a number.

And if you do want a fixed offset?

Then the Etc/GMT zones do it, and the sign is backwards. I am not going to explain that. Let's just look at it.

Query
SELECT '2024-01-15 12:00:00'::TIMESTAMP_NTZ                        AS UTC_NOON,
       CONVERT_TIMEZONE('UTC', 'Etc/GMT+5', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS ETC_GMT_PLUS_5,
       CONVERT_TIMEZONE('UTC', 'Etc/GMT-5', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS ETC_GMT_MINUS_5,
       CONVERT_TIMEZONE('UTC', 'Etc/GMT+0', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS ETC_GMT_ZERO;
Result1 row · 4 columns
UTC_NOONETC_GMT_PLUS_5ETC_GMT_MINUS_5ETC_GMT_ZERO
2024-01-15 12:00:002024-01-15 07:00:002024-01-15 17:00:002024-01-15 12:00:00

Start from noon in UTC. Etc/GMT+5 gives 07:00, which is UTC minus five. Etc/GMT-5 gives 17:00, which is UTC plus five.

So plus means behind. It is a real convention from the underlying zone database, it is not a Snowflake quirk, and it catches everybody at least once.

Now the reason you might want a fixed offset at all. Let's put a named zone next to a fixed one at two times of year.

Query
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS NY_IN_JANUARY,
       CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-07-15 12:00:00'::TIMESTAMP_NTZ)
         AS NY_IN_JULY,
       CONVERT_TIMEZONE('UTC', 'Etc/GMT+5', '2024-01-15 12:00:00'::TIMESTAMP_NTZ)
         AS FIXED_JANUARY,
       CONVERT_TIMEZONE('UTC', 'Etc/GMT+5', '2024-07-15 12:00:00'::TIMESTAMP_NTZ)
         AS FIXED_JULY;
Result1 row · 4 columns
NY_IN_JANUARYNY_IN_JULYFIXED_JANUARYFIXED_JULY
2024-01-15 07:00:002024-07-15 08:00:002024-01-15 07:00:002024-07-15 07:00:00

The named zone gives 07:00 in January and 08:00 in July, because its wall clock moved in between. The fixed offset gives 07:00 both times.

Let's measure that gap instead of looking at it, and put the real elapsed time beside it as a control.

Query
SELECT DATEDIFF('hour',
         CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-01-15 12:00:00'::TIMESTAMP_NTZ),
         CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-07-15 12:00:00'::TIMESTAMP_NTZ))
         AS NAMED_ZONE_JAN_TO_JUL,
       DATEDIFF('hour',
         CONVERT_TIMEZONE('UTC', 'Etc/GMT+5', '2024-01-15 12:00:00'::TIMESTAMP_NTZ),
         CONVERT_TIMEZONE('UTC', 'Etc/GMT+5', '2024-07-15 12:00:00'::TIMESTAMP_NTZ))
         AS FIXED_OFFSET_JAN_TO_JUL,
       DATEDIFF('hour', '2024-01-15 12:00:00'::TIMESTAMP_NTZ,
                        '2024-07-15 12:00:00'::TIMESTAMP_NTZ) AS THE_REAL_GAP;
Result1 row · 3 columns
NAMED_ZONE_JAN_TO_JULFIXED_OFFSET_JAN_TO_JULTHE_REAL_GAP
436943684368

Through the named zone, January to July measures 4369 hours. Through the fixed offset it measures 4368. And the real gap between those two instants is 4368.

So the named zone is an hour out, and the fixed one is not. Which is correct depends entirely on what you were asking: elapsed time, or what the clock on the wall said.

Now let's measure daylight saving instead of describing it

The 10th of March 2024 is the spring-forward day in New York. Let's ask how long that day was, midnight to midnight, and ask it three ways in one query.

Query
SELECT DATEDIFF('day', '2024-03-10 00:00:00'::TIMESTAMP_LTZ,
                       '2024-03-11 00:00:00'::TIMESTAMP_LTZ) AS DAYS_APART,
       DATEDIFF('hour', '2024-03-10 00:00:00'::TIMESTAMP_LTZ,
                        '2024-03-11 00:00:00'::TIMESTAMP_LTZ) AS HOURS_APART_IN_SESSION_TZ,
       DATEDIFF('hour',
         CONVERT_TIMEZONE('America/New_York', 'UTC', '2024-03-10 00:00:00'::TIMESTAMP_NTZ),
         CONVERT_TIMEZONE('America/New_York', 'UTC', '2024-03-11 00:00:00'::TIMESTAMP_NTZ))
         AS HOURS_APART_IN_NEW_YORK;
Result1 row · 3 columns
DAYS_APARTHOURS_APART_IN_SESSION_TZHOURS_APART_IN_NEW_YORK
12423

In days, the answer is 1. Obviously. In hours, measured in the session zone, which is UTC here, the answer is 24.

In hours, measured in New York, the answer is 23.

That day really was 23 hours long. DATEDIFF in days said 1 and was no help at all, because a day is a calendar thing and an hour is a clock thing.

Now the control, so we know this is the zone and not the function, with the autumn day beside it.

Query
SELECT DATEDIFF('hour',
         CONVERT_TIMEZONE('Asia/Kolkata', 'UTC', '2024-03-10 00:00:00'::TIMESTAMP_NTZ),
         CONVERT_TIMEZONE('Asia/Kolkata', 'UTC', '2024-03-11 00:00:00'::TIMESTAMP_NTZ))
         AS HOURS_IN_KOLKATA,
       DATEDIFF('hour',
         CONVERT_TIMEZONE('America/New_York', 'UTC', '2024-11-03 00:00:00'::TIMESTAMP_NTZ),
         CONVERT_TIMEZONE('America/New_York', 'UTC', '2024-11-04 00:00:00'::TIMESTAMP_NTZ))
         AS HOURS_ON_THE_FALL_BACK_DAY;
Result1 row · 2 columns
HOURS_IN_KOLKATAHOURS_ON_THE_FALL_BACK_DAY
2425

Kolkata over the same two dates gives 24, because Kolkata has no daylight saving. And the fall-back day in New York gives 25.

So 23, 24 and 25 are all real answers to “how long is a day”, and which one you get depends on the zone you ask in.

There is also an hour that does not exist

On that same spring-forward day, the New York clock jumps straight from one o'clock to three. Let's look at it from either side.

Query
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-03-10 06:30:00'::TIMESTAMP_NTZ)
         AS UTC_0630_IN_NY,
       CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-03-10 07:30:00'::TIMESTAMP_NTZ)
         AS UTC_0730_IN_NY,
       TRY_TO_TIMESTAMP('2024-03-10 02:30:00', 'YYYY-MM-DD HH24:MI:SS')
         AS PARSING_THE_MISSING_HOUR;
Result1 row · 3 columns
UTC_0630_IN_NYUTC_0730_IN_NYPARSING_THE_MISSING_HOUR
2024-03-10 01:30:002024-03-10 03:30:002024-03-10 02:30:00

06:30 in UTC is 01:30 in New York. 07:30 in UTC is 03:30. So nothing in New York was two-something that day.

Now look at the last column. We asked TRY_TO_TIMESTAMP to parse 2024-03-10 02:30:00, and it did it quite happily.

That works because the result is a TIMESTAMP_NTZ, and an NTZ has no zone, so it has no missing hour either.

Worth sitting with

That is the whole problem with NTZ in one line. It cannot be wrong, because it does not know anything. Which is genuinely what you want for a wall clock, and genuinely not what you want for an event.

Now let's try it on real data

Enough literals. The ORDERS table has a DATE column, and a date is midnight. Let's read those dates in three zones.

Query
SELECT ORDER_ID, ORDER_DATE,
       CONVERT_TIMEZONE('UTC', 'America/New_York', ORDER_DATE::TIMESTAMP_NTZ) AS NEW_YORK,
       CONVERT_TIMEZONE('UTC', 'Europe/London',    ORDER_DATE::TIMESTAMP_NTZ) AS LONDON,
       CONVERT_TIMEZONE('UTC', 'Asia/Tokyo',       ORDER_DATE::TIMESTAMP_NTZ) AS TOKYO
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;
Result10 rows · 5 columns
ORDER_IDORDER_DATENEW_YORKLONDONTOKYO
10012023-01-122023-01-11 19:00:002023-01-12 00:00:002023-01-12 09:00:00
10022023-01-282023-01-27 19:00:002023-01-28 00:00:002023-01-28 09:00:00
10032023-02-142023-02-13 19:00:002023-02-14 00:00:002023-02-14 09:00:00
10042023-02-272023-02-26 19:00:002023-02-27 00:00:002023-02-27 09:00:00
10052023-03-092023-03-08 19:00:002023-03-09 00:00:002023-03-09 09:00:00
10062023-03-222023-03-21 20:00:002023-03-22 00:00:002023-03-22 09:00:00
10072023-04-052023-04-04 20:00:002023-04-05 01:00:002023-04-05 09:00:00
10082023-04-192023-04-18 20:00:002023-04-19 01:00:002023-04-19 09:00:00
10092023-05-022023-05-01 20:00:002023-05-02 01:00:002023-05-02 09:00:00
10102023-05-212023-05-20 20:00:002023-05-21 01:00:002023-05-21 09:00:00

Take the first row. The order date is 2023-01-12. In New York it is 2023-01-11 19:00:00, which is the day before. In London it stays on the 12th at midnight. In Tokyo it is 09:00:00 on the 12th.

Two other things are visible in that grid and both are daylight saving. The New York column moves from 19:00:00 to 20:00:00 partway down, and the London column moves from 00:00:00 to 01:00:00. Neither of those changes the date, but both change the clock.

So how many of the orders change date? Let's count it rather than read ten rows and guess.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(ORDER_DATE
                <> CONVERT_TIMEZONE('UTC', 'America/New_York',
                                    ORDER_DATE::TIMESTAMP_NTZ)::DATE) AS MOVED_IN_NEW_YORK,
       COUNT_IF(ORDER_DATE
                <> CONVERT_TIMEZONE('UTC', 'Asia/Tokyo',
                                    ORDER_DATE::TIMESTAMP_NTZ)::DATE) AS MOVED_IN_TOKYO
FROM ORDERS;
Result1 row · 3 columns
ROWS_TOTALMOVED_IN_NEW_YORKMOVED_IN_TOKYO
41410

Forty-one rows. Forty-one changed date in New York. None changed date in Tokyo.

All of them

That is every single row, not an edge case and not a rounding issue. A DATE is midnight, and midnight read west of UTC is the previous evening. This is what happens when a date column gets read in local time without anybody deciding that it should be.

One value end to end

Let's finish the working part with one value seen five ways in a single row: stored, as an LTZ, converted, as an epoch, and formatted.

Query
SELECT '2024-06-30 14:37:52'::TIMESTAMP_NTZ                                       AS STORED_NTZ,
       '2024-06-30 14:37:52'::TIMESTAMP_LTZ                                       AS AS_LTZ,
       CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', '2024-06-30 14:37:52'::TIMESTAMP_NTZ) AS IN_KOLKATA,
       DATE_PART('epoch_second', '2024-06-30 14:37:52'::TIMESTAMP_NTZ)            AS EPOCH_,
       TO_CHAR('2024-06-30 14:37:52'::TIMESTAMP_NTZ, 'YYYY-MM-DD HH24:MI:SS')     AS FORMATTED;
Result1 row · 5 columns
STORED_NTZAS_LTZIN_KOLKATAEPOCH_FORMATTED
2024-06-30 14:37:522024-06-30 14:37:52+00:002024-06-30 20:07:5217197582722024-06-30 14:37:52

Same instant in all five columns. The only thing that changes is which question we asked about it.

One last thing: put the session back

We changed a session setting partway through this post, and a setting left changed is a setting that will confuse somebody later. Let's unset it.

Query
ALTER SESSION UNSET TIMEZONE;
Result1 row · 1 column
status
Statement executed successfully.

And let's check rather than assume.

Query
SHOW PARAMETERS LIKE 'TIMEZONE' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
TIMEZONEAmerica/Los_AngelesAmerica/Los_Angelestime zoneSTRING

Back to America/Los_Angeles, which is where this session started. UNSET returns a parameter to its default rather than to some value you have to remember.

Key points

  • TIMESTAMP_NTZ stores a wall clock with no zone. TIMESTAMP_LTZ stores an instant and shows it in your session's zone. TIMESTAMP_TZ stores an instant and keeps the offset it came with.
  • Plain TIMESTAMP is not a fourth type. It is whatever TIMESTAMP_TYPE_MAPPING says, which was TIMESTAMP_NTZ here.
  • Casting a TIMESTAMP_TZ to TIMESTAMP_NTZ keeps the digits and throws the offset away, which moves the actual instant. Proved in epoch seconds: 19,800 seconds, with no error and no warning. Cast to TIMESTAMP_LTZ instead and the epoch is unchanged.
  • An NTZ and an LTZ with the same digits compare equal, because the comparison reads the NTZ in the session zone.
  • The session time zone is a display setting for LTZ. It changed nothing about what an NTZ or a TZ gave back.
  • CONVERT_TIMEZONE takes two arguments on a zoned value and three on an NTZ. Two arguments return a TIMESTAMP_TZ, three return a TIMESTAMP_NTZ.
  • An offset is fine inside a literal and is not a zone name. Etc/GMT+5 is UTC minus five, so the sign really is backwards.
  • Daylight saving, measured: the spring-forward day in New York was 23 hours, the fall-back day was 25, and Kolkata, which has no daylight saving, was 24.
  • There is an hour that does not exist on the spring-forward day, and TRY_TO_TIMESTAMP parses it anyway, because an NTZ has no zone and therefore no missing hour.
  • All 41 order dates changed date when read in New York, and none of them did in Tokyo. A DATE is midnight, and that is not an edge case.
Next in this series

Episode 77 — conversion and casting. CAST, TO_NUMBER, TO_VARCHAR and TO_BOOLEAN, what Snowflake converts for you without being asked, and a flag column that comes out wrong on every row.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The ORDERS data is invented demo data created by the course setup script. Every offset, clock time and epoch number named in the text above is read off a captured column rather than worked out here, and the session time zone the grids were captured under is shown in the first section. Nothing on this page is timed and no performance claims are made anywhere in this series. Function behaviour follows the Snowflake CONVERT_TIMEZONE documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)