For a convert timezone Snowflake task, record the input type, function signature, source-zone rule, session, and returned type. Inspect the logical value before treating formatted clock text as evidence.
This original packet prepares fixed-input SELECT specifications for January 15, 2024. No Snowflake account, query execution, session change, database file, or cost-incurring operation has been performed. Actual typed results and instance metadata remain blank.
Choose the signature deliberately
Snowflake documents two CONVERT_TIMEZONE forms:
| Form | Source interpretation | Documented return type |
|---|---|---|
| Three arguments | Explicit source zone for an NTZ input | TIMESTAMP_NTZ |
| Two arguments | From the input value/type; NTZ uses session zone | TIMESTAMP_TZ |
The result's display format also depends on the session and timestamp type. Use that distinction for review rather than an assumed fixed display string. Snowflake's conversion signatures and return types.
In this packet, a wall clock means date/time fields without an attached zone. An offset-bearing input supplies an instant through those fields and its offset. Do not make an NTZ value carry a zone merely by describing it as “UTC noon.”
C1: three arguments preserve the explicit wall-clock contract
The authored SELECT specification is:
SELECT CONVERT_TIMEZONE(
'UTC',
'UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ
) AS c1;
The input is explicitly NTZ. The first UTC supplies source interpretation; the second is the target. The expected logical result is 2024-01-15 12:00:00, TIMESTAMP_NTZ.
The source and target happen to be identical, so the wall-clock fields stay noon. The returned NTZ does not acquire an attached UTC zone or offset.
Keep the source-zone argument in the native evidence record. If someone exports only the NTZ value, its date/time fields do not preserve that original argument as attached timezone metadata.
C2: two arguments use the explicit input offset
Use:
SELECT CONVERT_TIMEZONE(
'UTC',
'2024-01-15 12:00:00 +0200'::TIMESTAMP_TZ
) AS c2;
The authored input includes +0200 and is explicitly TZ. Twelve o'clock at that offset corresponds to ten o'clock UTC:
12:00 − 02:00 = 10:00 UTC on the same date.
The expected logical result is 2024-01-15 10:00:00 with +0000, TIMESTAMP_TZ. The converted value represents the same instant as the explicit-offset input.
This expected value is a mathematical trace, not a captured Snowflake result. The actual timestamp formatting may include fractional seconds or another configured presentation; compare the returned logical fields, offset, and type.
C3: two arguments on NTZ require a recorded session
Under a declared session TIMEZONE = UTC, prepare:
SELECT CONVERT_TIMEZONE(
'UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ
) AS c3;
The expected logical result is 2024-01-15 12:00:00 with +0000, TIMESTAMP_TZ under that declared session. The source interpretation comes from the session because the input has no timezone.
The session condition is a fixture assumption that must be verified on an authorized instance later. It has not been set or observed here. No ALTER SESSION statement is executed or provided as a completed action.
For another confirmed IANA session zone, create a separate case with its actual session metadata and fresh observations. Do not carry C3's noon result into the revised case or invent a numeric local offset without checking the relevant setup.
C1 and C3 can display the same hour under UTC, while returning different types and using different source-zone rules. That is why display equality is insufficient.
Keep casts and formatting in their own columns
A subsequent cast is another operation. If someone casts C1's NTZ result to TZ, record the cast and its interpretation separately. Do not describe the new value as evidence that the original three-argument conversion returned TZ or retained its source-zone argument.
Likewise, a formatted string with an added UTC label is presentation. It does not establish the underlying type or prove the session setting.
Use this native ledger:
| Case | Input type/value | Signature/source rule | Expected logical result/type | Actual typed observation |
|---|---|---|---|---|
| C1 | NTZ, 2024-01-15 noon | Three arguments, UTC → UTC | Noon, NTZ; no attached offset | |
| C2 | TZ, noon +0200 | Two arguments; offset from input | 10:00 +0000, TZ | |
| C3 | NTZ, noon | Two arguments; declared session UTC | Noon +0000, TZ |
Keep output-format observations outside the logical-value column. Preserve original input type rather than relying on a generic timestamp cast that changes meaning with configuration.
Record the active timezone database instead of assuming a cached release
The current official conversion page refers to the active IANA Time Zone Database and identifies SYSTEM$GET_TZDB_VERSION for determining its release. It does not justify labeling an unknown account with a fixed cached release number. Snowflake's active database note.
Record actual function availability and result only during an authorized instance check. Neither has been observed in this manuscript.
Keep these fields with the test source:
| Environment field | Actual observation |
|---|---|
| Instance/version or applicable native environment record | |
| Session TIMEZONE | |
| Timestamp output format settings | |
| Active TZDB release and observation method | |
| Source/query revision | |
| Any post-conversion cast |
Use the documented names with their exact case and quoting. Unsupported zone strings or types need actual error observations; this packet supplies no invented diagnostic text or universal daylight-saving gap/overlap policy.
Separate NULL from invalid input
Snowflake documents NULL output when any argument is NULL. Prepare that as a distinct expected case, such as a NULL target argument. It is not a guessed replacement for an invalid zone, malformed timestamp, missing input record, or unexpected type.
For this review packet, unresolved required inputs hold acceptance until defined. When native tests run later, preserve actual errors separately from legitimate NULL results. Do not turn errors into noon or silently fill a zone with the session default.
Changed input type, literal, source/target zone, session, active database, cast, or source revision retires earlier acceptance for the changed case. Keep historical observations tied to their original setup.
No daylight-saving ambiguity, gap handling, live account version, or deployment behavior has been tested.
Copy this prompt for the typed-result packet
Create one complete, self-contained US-English HTML companion for “Snowflake • inspect the timestamp contract”. Return the full HTML document, with inline CSS and any JavaScript needed for the local interactions. Use the original scenario, named records, numbers and expected cases below.
Design the page for this task: use clearly labeled input or source records, a readable result or worked example, and an adjacent explanation of the evidence. Use a restrained visual hierarchy, left-aligned text, readable tables or stacked records, and a responsive layout at 720px and 360px. Provide meaningful labeled controls for C1 · explicit NTZ; C2 · offset TZ; C3 · session NTZ; Calculate expectation; Reset fixtures; Select text. Use keyboard-accessible native controls and visible focus. Keep the complete content readable without an external dependency.
The following brief supplies the substantive content and constraints. Native-format requests describe the material to represent or copy manually; this HTML must not pretend to create DOCX, XLSX, PDF, PPTX, H5P or another native file, run Python/Flask/Django/Snowflake/Power BI, connect a live service, send messages, or perform an authorized real-world action. Where the task requires a native source, actual observation or separate approval, retain that requirement and leave unsupplied evidence blank.
Prepare an original English Snowflake timezone-conversion packet. Do not create SQL files, connect an account, execute queries, alter
sessions, create/change database objects, call APIs, incur compute costs,
capture screens, or publish.
Fixed date January 15, 2024. Use explicitly typed inputs and SELECT specifications inside manuscript:
C1 CONVERT_TIMEZONE('UTC','UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ). Expected logical noon, TIMESTAMP_NTZ, no attached zone/offset. C2 CONVERT_TIMEZONE('UTC',
'2024-01-15 12:00:00 +0200'::TIMESTAMP_TZ). Expected 10:00 +0000, TIMESTAMP_TZ, same instant as offset input. C3 CONVERT_TIMEZONE('UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ)
under declared but unobserved session TIMEZONE UTC. Expected noon +0000, TIMESTAMP_TZ. Actual session confirmation and results remain blank.
Record signature, explicit input type/value, source-zone rule, target,
session TIMEZONE, timestamp output-format settings and active TZDB. C1 explicit source argument differs from C3 session interpretation. C2 uses input offset. Formatted clock text alone is not type evidence. Three-argument NTZ does not retain the source argument as attached metadata. Post-conversion cast has separate provenance, not original return-type proof.
Current official reference identifies SYSTEM$GET_TZDB_VERSION for active
TZDB release. Actual instance availability/result require authorized
checks later. No fixed cached release, installed version or account claim. Use exact documented zone names/case/quoting. Additional confirmed IANA session case needs fresh actual observations,
not an invented local-time result.
NULL argument -> documented expected NULL. Invalid zone/type/malformed/missing input is a separate held/error case,
not guessed NULL or default clock. Actual diagnostic strings pending. No daylight-saving overlap/gap policy inferred.
Deliver native SELECT specifications, typed logical expectations,
environment/cast ledger and blank actual type/value/offset/format/error
observations. Changes to input/session/database/source/cast retire old
acceptance; preserve historical evidence with original setup.
Native SQL/type/session evidence remains authoritative. Limit the NOS connection to an optional Explore NOS link without feature promises; keep SQL execution and database conversion evidence in the native workflow. Visual/article-width/narrow and reading-interface checks remain pending.
COMPLETE AUTHORED SCENARIO AND EXPECTED CASES
The following is the supplied reading/teaching/calculation record to represent in the companion. Preserve its particular content, examples, source references and unknowns. Wording about a future native implementation refers to that native task, not permission to omit this HTML companion.
# Convert Timezone Snowflake: Check the Timestamp Type Behind the Clock
Two query results both show noon. One is an unzoned wall-clock value; the other carries a UTC offset. You copy the display text into a comparison table and conclude the conversions mean the same thing.
For a convert timezone Snowflake task, record the input type, function signature, source-zone rule, session, and returned type. Inspect the logical value before treating formatted clock text as evidence.
This original packet prepares fixed-input SELECT specifications for January 15, 2024. No Snowflake account, query execution, session change, database file, or cost-incurring operation has been performed. Actual typed results and instance metadata remain blank.
## Choose the signature deliberately
Snowflake documents two CONVERT_TIMEZONE forms:
| Form | Source interpretation | Documented return type |
| --- | --- | --- |
| Three arguments | Explicit source zone for an NTZ input | TIMESTAMP_NTZ |
| Two arguments | From the input value/type; NTZ uses session zone | TIMESTAMP_TZ |
The result's display format also depends on the session and timestamp type. Use that distinction for review rather than an assumed fixed display string. [Snowflake's conversion signatures and return types](https://docs.snowflake.com/en/sql-reference/functions/convert_timezone).
In this packet, a wall clock means date/time fields without an attached zone. An offset-bearing input supplies an instant through those fields and its offset. Do not make an NTZ value carry a zone merely by describing it as “UTC noon.”
## C1: three arguments preserve the explicit wall-clock contract
The authored SELECT specification is:
SELECT CONVERT_TIMEZONE(
'UTC',
'UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ
) AS c1;
The input is explicitly NTZ. The first UTC supplies source interpretation; the second is the target. The expected logical result is **2024-01-15 12:00:00, TIMESTAMP_NTZ**.
The source and target happen to be identical, so the wall-clock fields stay noon. The returned NTZ does not acquire an attached UTC zone or offset.
Keep the source-zone argument in the native evidence record. If someone exports only the NTZ value, its date/time fields do not preserve that original argument as attached timezone metadata.
## C2: two arguments use the explicit input offset
Use:
SELECT CONVERT_TIMEZONE(
'UTC',
'2024-01-15 12:00:00 +0200'::TIMESTAMP_TZ
) AS c2;
The authored input includes +0200 and is explicitly TZ. Twelve o'clock at that offset corresponds to ten o'clock UTC:
**12:00 − 02:00 = 10:00 UTC on the same date.**
The expected logical result is **2024-01-15 10:00:00 with +0000, TIMESTAMP_TZ**. The converted value represents the same instant as the explicit-offset input.
This expected value is a mathematical trace, not a captured Snowflake result. The actual timestamp formatting may include fractional seconds or another configured presentation; compare the returned logical fields, offset, and type.
## C3: two arguments on NTZ require a recorded session
Under a **declared session TIMEZONE = UTC**, prepare:
SELECT CONVERT_TIMEZONE(
'UTC',
'2024-01-15 12:00:00'::TIMESTAMP_NTZ
) AS c3;
The expected logical result is **2024-01-15 12:00:00 with +0000, TIMESTAMP_TZ** under that declared session. The source interpretation comes from the session because the input has no timezone.
The session condition is a fixture assumption that must be verified on an authorized instance later. It has not been set or observed here. No ALTER SESSION statement is executed or provided as a completed action.
For another confirmed IANA session zone, create a separate case with its actual session metadata and fresh observations. Do not carry C3's noon result into the revised case or invent a numeric local offset without checking the relevant setup.
C1 and C3 can display the same hour under UTC, while returning different types and using different source-zone rules. That is why display equality is insufficient.
## Keep casts and formatting in their own columns
A subsequent cast is another operation. If someone casts C1's NTZ result to TZ, record the cast and its interpretation separately. Do not describe the new value as evidence that the original three-argument conversion returned TZ or retained its source-zone argument.
Likewise, a formatted string with an added UTC label is presentation. It does not establish the underlying type or prove the session setting.
Use this native ledger:
| Case | Input type/value | Signature/source rule | Expected logical result/type | Actual typed observation |
| --- | --- | --- | --- | --- |
| C1 | NTZ, 2024-01-15 noon | Three arguments, UTC → UTC | Noon, NTZ; no attached offset | |
| C2 | TZ, noon +0200 | Two arguments; offset from input | 10:00 +0000, TZ | |
| C3 | NTZ, noon | Two arguments; declared session UTC | Noon +0000, TZ | |
Keep output-format observations outside the logical-value column. Preserve original input type rather than relying on a generic timestamp cast that changes meaning with configuration.
## Record the active timezone database instead of assuming a cached release
The current official conversion page refers to the active IANA Time Zone Database and identifies `SYSTEM$GET_TZDB_VERSION` for determining its release. It does not justify labeling an unknown account with a fixed cached release number. [Snowflake's active database note](https://docs.snowflake.com/en/sql-reference/functions/convert_timezone).
Record actual function availability and result only during an authorized instance check. Neither has been observed in this manuscript.
Keep these fields with the test source:
| Environment field | Actual observation |
| --- | --- |
| Instance/version or applicable native environment record | |
| Session TIMEZONE | |
| Timestamp output format settings | |
| Active TZDB release and observation method | |
| Source/query revision | |
| Any post-conversion cast | |
Use the documented names with their exact case and quoting. Unsupported zone strings or types need actual error observations; this packet supplies no invented diagnostic text or universal daylight-saving gap/overlap policy.
## Separate NULL from invalid input
Snowflake documents NULL output when any argument is NULL. Prepare that as a distinct expected case, such as a NULL target argument. It is not a guessed replacement for an invalid zone, malformed timestamp, missing input record, or unexpected type.
For this review packet, unresolved required inputs hold acceptance until defined. When native tests run later, preserve actual errors separately from legitimate NULL results. Do not turn errors into noon or silently fill a zone with the session default.
Changed input type, literal, source/target zone, session, active database, cast, or source revision retires earlier acceptance for the changed case. Keep historical observations tied to their original setup.
No daylight-saving ambiguity, gap handling, live account version, or deployment behavior has been tested.
Implement the local companion rather than returning a text-only plan. Preserve every numerical fixture and the distinction between illustrative expectations and actual evidence. Any native-runtime or classroom tests mentioned above remain tests to perform in the proper environment; do not mark them passed. Keep edits and practice responses in this open page only, without claiming persistent storage. Provide invalid/missing-input feedback and reset behavior that agree with this specific brief. Do not claim accessibility certification, learning gains, real approvals or test results that were not observed.
Review the prepared typed-result comparison
After native tests, a reading copy can put C1/C2/C3 beside their actual types, logical values, session fields, and display strings. The review task is to see what an identical clock hour does and does not establish.
Keep native SQL, typed outputs, session/database metadata, and cast records authoritative. The HTML reading copy supplies no evidence of SQL execution or an account's timezone behavior.
Leave the reviewer with the type and source interpretation behind each clock value.