SNOW-1729244: Issue with Large TIMESTAMP_TZ Insertion via SnowflakeDbCommand in .NET

[skornsek]

Description: There appears to be a bug when inserting large timestamps (e.g., 9999-12-30T23:00:00.0000000+00:00) into a TIMESTAMP_TZ column using the SnowflakeDbCommand in .NET. The issue does not occur when inserting the same value manually via Snowsight.

Steps to Reproduce:

1. Create a Snowflake table with a TIMESTAMP_TZ column, such as:

   CREATE TABLE DATES_TESTING1 (
       ID NUMBER IDENTITY,
       CODE STRING NOT NULL,
       EFF_DT TIMESTAMP_TZ,
       END_DT TIMESTAMP_TZ,
       MODIFIED TIMESTAMP_TZ
   );

2. Execute the following .NET code to insert the large timestamp:

   using Snowflake.Data.Client;
   using Snowflake.Data.Core;
   
   string connectionString = "CONNECTION_STRING";
   
   using (var connection = new SnowflakeDbConnection())
   {
       connection.ConnectionString = connectionString;
       connection.Open();
   
       string sqlCommand = @"
                   INSERT INTO ""DATES_TESTING1"" (""CODE"", ""MODIFIED"", ""EFF_DT"", ""END_DT"")
                   SELECT  :p1, :p2, :p3, :p4";
   
       using (var command = new SnowflakeDbCommand())
       {
           command.Connection = connection;
           command.CommandText = sqlCommand;
   
           command.Parameters.Add(new SnowflakeDbParameter("p1", SFDataType.TEXT) { Value = "DOTNET 4.1.0" });
           command.Parameters.Add(new SnowflakeDbParameter("p2", SFDataType.TIMESTAMP_TZ) { Value = DateTimeOffset.Parse("2024-10-08T16:14:12.5910721+00:00") });
           command.Parameters.Add(new SnowflakeDbParameter("p3", SFDataType.TIMESTAMP_TZ) { Value = DateTimeOffset.Parse("1899-12-31T23:00:00.0000000+00:00") });
           command.Parameters.Add(new SnowflakeDbParameter("p4", SFDataType.TIMESTAMP_TZ) { Value = DateTimeOffset.Parse("9999-12-30T23:00:00.0000000+00:00") });
   
           try
           {
               int rowsAffected = command.ExecuteNonQuery();
               Console.WriteLine($"{rowsAffected} rows inserted successfully.");
           }
           catch (Exception ex)
           {
               Console.WriteLine($"Error executing command: {ex.Message}");
           }
       }
   }

3. Insert the same values manually via a SQL query using Snowsight, and the END_DT is stored correctly.

   INSERT INTO DATES_TESTING1 (CODE, MODIFIED, EFF_DT, END_DT)
   SELECT 'MANUAL', '2024-10-08T16:14:12.5910721+00:00', '1899-12-31T23:00:00.0000000+00:00', '9999-12-30T23:00:00.0000000+00:00';

4. Check the DATES_TESTING1 table, and observe that the END_DT is stored as '1816-03-29 04:56:08.066 +0000' instead of the expected value '9999-12-30T23:00:00.0000000+00:00'.

   Select * FROM DATES_TESTING1;

   

Expected Behavior: The large timestamp value (9999-12-30T23:00:00.0000000+00:00) should be correctly inserted into the TIMESTAMP_TZ column using SnowflakeDbCommand.

Actual Behavior: When using SnowflakeDbCommand to insert the large timestamp, the value is stored as '1816-03-29 04:56:08.066 +0000', which is incorrect. Dates around 2300-12-31 work. Dates bigger than 2500-12-31 also get corrupted.

Additional Information:

• .NET SDK version: 4.1.0
• Database: Snowflake
• SnowflakeDbCommand library: Snowflake.Data.Core
• Windows 11, 64-bit operating system, x64-based processor
• .NET SDK 8.0.400

This bug seems to affect large timestamp values when inserted via .NET, but works correctly with manual SQL queries executed in Snowsight.

[sfc-gh-dszmolka]

thank you for raising this issue and providing useful details! we'll take a look

[skornsek]

I was debugging some more and I think the issue could probably be here in this piece of code:

namespace Snowflake.Data.Core;

internal static class SFDataConverter
...

internal static string csharpValToSfVal(SFDataType sfDataType, object srcVal)

...
case SFDataType.TIMESTAMP_TZ:
{
    if (srcVal.GetType() != typeof(DateTimeOffset))
    {
        throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal, srcVal.GetType().ToString(), DbType.DateTimeOffset.ToString());
    }
    DateTimeOffset dateTimeOffset = (DateTimeOffset)srcVal;
    long utcTicks2 = dateTimeOffset.UtcTicks;
    DateTime unixEpoch = UnixEpoch;
    result = $"{(utcTicks2 - unixEpoch.Ticks) * 100} {dateTimeOffset.Offset.TotalMinutes + 1440.0}";
    break;
}

For the dates above these values are generated:

• UtcDateTime = {08/10/2024 16:14:12} -> 1728404052591072100 1440
• UtcDateTime = {31/12/1899 23:00:00} -> -2208992400000000000 1440
• UtcDateTime = {30/12/9999 23:00:00} -> -4852206231933722624 1440

In Snowflake I get these values:

-- Test timestamp for 1728404052591072100 ticks with 1440 offset (UTC)
SELECT TO_TIMESTAMP_TZ((1728404052591072100 / 1000000000) + ' 1440');
-- 2024-10-08 09:38:12.591 -0700

-- Test timestamp for -2208992400000000000 ticks with 1440 offset (UTC)
SELECT TO_TIMESTAMP_TZ((-2208992400000000000 / 1000000000) + ' 1440');
-- 1899-12-31 15:24:00.000 -0800

-- Test timestamp for -4852206231933722624 ticks with 1440 offset (UTC)
SELECT TO_TIMESTAMP_TZ((-4852206231933722624 / 1000000000) + ' 1440');
-- 1816-03-28 21:28:08.066 -0752

[sfc-gh-dszmolka]

also linking the PR we have for the fix https://github.com/snowflakedb/snowflake-connector-net/pull/1038

[sfc-gh-dszmolka]

fix is merged and will be included with the next .NET driver release cycle

[sfc-gh-dszmolka]

released with Snowflake .NET driver v4.2.0 in October 2024 release cycle