-
Notifications
You must be signed in to change notification settings - Fork 140
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SNOW-1729244: Issue with Large TIMESTAMP_TZ Insertion via SnowflakeDbCommand in .NET #1036
Comments
thank you for raising this issue and providing useful details! we'll take a look |
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:
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 |
also linking the PR we have for the fix #1038 |
fix is merged and will be included with the next .NET driver release cycle |
released with Snowflake .NET driver v4.2.0 in October 2024 release cycle |
Description:
There appears to be a bug when inserting large timestamps (e.g.,
9999-12-30T23:00:00.0000000+00:00
) into aTIMESTAMP_TZ
column using theSnowflakeDbCommand
in .NET. The issue does not occur when inserting the same value manually via Snowsight.Steps to Reproduce:
Create a Snowflake table with a
TIMESTAMP_TZ
column, such as:Execute the following .NET code to insert the large timestamp:
Insert the same values manually via a SQL query using Snowsight, and the
END_DT
is stored correctly.Check the
DATES_TESTING1
table, and observe that theEND_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'
.Expected Behavior:
The large timestamp value (
9999-12-30T23:00:00.0000000+00:00
) should be correctly inserted into theTIMESTAMP_TZ
column usingSnowflakeDbCommand
.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 than2500-12-31
also get corrupted.Additional Information:
This bug seems to affect large timestamp values when inserted via .NET, but works correctly with manual SQL queries executed in Snowsight.
The text was updated successfully, but these errors were encountered: