Snowflake: conversion of timestamp NTZ to UTC with offset

Thursday, Apr 17, 2025 | 1 minute read | Updated at Thursday, Apr 17, 2025

@ Geoff Corey

Problem

Code attempting to transform Snowflake timestamp_ntz values stored in America/New_York local format into UTC time with offset notation did not account for Daylight Savings Time.

Solution

ALTER SESSION SET TIMEZONE = 'America/New_York';
SELECT
    TO_TIMESTAMP_NTZ('12/15/2023 01:02:03', 'mm/dd/yyyy hh24:mi:ss') as mytimestamp,
    mytimestamp::timestamp_tz as orig,
    TO_VARCHAR(mytimestamp, 'YYYY-MM-DDTHH24:MI:SS.FF3-TZHTZM') as datetime_wrong,
    CONVERT_TIMEZONE('UTC','America/New_York',TO_TIMESTAMP_LTZ(mytimestamp)) AS converted_ts,
    DATEDIFF('hour', converted_ts, mytimestamp) as hourdiff,
    TO_VARCHAR(converted_ts) || '-0' || hourdiff || ':00' as datetime;

The key involves using CONVERT_TIMEZONE() with explicit source and target timezones, which properly handles DST transitions.

Previous page Caddy, Wildcard Certs and Ghost

Professional Experience

IBM - Software Developer

Oct 2015 - Aug 2019 | Durham, NC

Developed open-source continuous delivery services for Kubernetes

Built IBM Cloud Kubernetes Service API/CLI with GoLang and Docker

Created IBM Bluemix DevOps tools using Cloud Foundry and Node.js

Patent filed Mar 31, 2017: “Container chaining for automated process completion” (US20180285156A1)

UDU LLC - Application Architect

Dec 2014 - Sep 2015 | Durham, NC

Reduced infrastructure costs by 60%

Designed REST services with MongoDB, Redis, Node.js on AWS

Developed chat service using Node.js and Faye

Created microservices for image manipulation

Evaluated Docker container architectures

TotalCast LLC - Director of Technology

Mar 2014 - Dec 2014 | Raleigh, NC

Commercialized patents for Capitol Broadcasting Company

Managed TV watch app development for WRAZ FOX50

Designed DRM server for encrypted Apple HLS streams in GoLang

Built auto-scaling architecture on AWS

Additional Notable Positions

Ignite - Contract Software Engineer (Nov 2013 - Feb 2014)

6fusion USA - Senior Software Engineer (Sep 2011 - Oct 2013)

Independent Consultant (Jul 2008 - Sep 2011)

National Cinemedia/Creoss - Program Manager (Mar 2011 - Jul 2011)

ESRG Tech/Creoss - Program Manager (Nov 2009 - Dec 2010) - U.S. Navy engine monitoring systems

Digitalsmiths - Senior Software Engineer (Sep 2008 - Nov 2009)

GameVee.com - Development Lead/Application Architect (Feb 2007 - Sep 2008)

Pheasant Creek Coffee LLC - Owner (Jul 2004 - Feb 2008)

Sprint - Senior Manager (Jul 2001 - Jul 2004) - Led team of 30+ for SprintPCS.com

Red Sky Interactive - Team Lead (Sep 2000 - Jul 2001)

Online Insight, Inc - Manager of Technical Services (Jul 1999 - Sep 2000)

McKesson HBOC - Software Engineer (Nov 1998 - Jul 1999)

ClientLink, Inc - Director of Object Technologies (Feb 1997 - Aug 1998)

Additional roles from 1990-1996 in database and software engineering

Container Chaining for Automated Process Completion

This patent describes “a method obtains a configuration for an automated process that includes discrete tasks.” The system allows users to specify different container images for each task and define the sequence for instantiating containers to complete the process. The method automatically identifies the next container to execute and repeats this process until all tasks are finished.

Intra-Media Demarcation

This patent presents a mechanism enabling users to “mark one or more sections of the media’s timeline for later reference.” The system stores demarcation data as metadata while preserving the original media intact. An enhanced player can then display these highlighted sections during playback without modifying the source material, allowing viewers to experience both the complete media and the annotated portions.