-
Notifications
You must be signed in to change notification settings - Fork 137
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
Labels
Comments
github-actions
bot
changed the title
Issue with Large TIMESTAMP_TZ Insertion via SnowflakeDbCommand in .NET
SNOW-1729244: Issue with Large TIMESTAMP_TZ Insertion via SnowflakeDbCommand in .NET
Oct 9, 2024
sfc-gh-dszmolka
assigned sfc-gh-dszmolka and sfc-gh-snow-drivers-warsaw-dl and unassigned sfc-gh-dszmolka
Oct 9, 2024
sfc-gh-dszmolka
added
the
status-triage_done
Initial triage done, will be further handled by the driver team
label
Oct 9, 2024
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 |
Open
6 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Labels
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: