SqlDbType - DateTime, DateTime2, DateTimeOffset의 차이점
SQL 서버 입장이 아닌, 닷넷 개발자 입장에서의 차이점을 알아볼까요?
예제 DB 에 다음과 같은 테이블을 정의하고,
CREATE TABLE [dbo].[TestTable](
[Id] [int] NOT NULL,
[Time1] [datetime] NULL,
[Time2] [datetime2](7) NULL,
[TimeOffset] [datetimeoffset](7) NULL
) ON [PRIMARY]
테스트를 위해 다음과 같은 예제 코드를 작성합니다.
using (SqlConnection sqlConnection =
new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=."))
{
sqlConnection.Open();
// Delete
SqlCommand deleteCommand = new SqlCommand();
deleteCommand.Connection = sqlConnection;
deleteCommand.CommandText = "DELETE FROM TESTTABLE";
deleteCommand.ExecuteNonQuery();
// Create
SqlCommand insertCommand = new SqlCommand();
insertCommand.Connection = sqlConnection;
insertCommand.CommandText = "INSERT INTO TESTTABLE(Id, Time1, Time2, TimeOffset) VALUES (@Id, @Time1, @Time2, @TimeOffset)";
insertCommand.Parameters.Add("@Id", SqlDbType.Int);
insertCommand.Parameters.Add("@Time1", SqlDbType.DateTime);
insertCommand.Parameters.Add("@Time2", SqlDbType.DateTime2);
insertCommand.Parameters.Add("@TimeOffset", SqlDbType.DateTimeOffset);
insertCommand.Parameters[0].Value = Guid.NewGuid().GetHashCode();
DateTime now = DateTime.UtcNow;
insertCommand.Parameters[1].Value = now;
insertCommand.Parameters[2].Value = now;
insertCommand.Parameters[3].Value = now;
int affected = insertCommand.ExecuteNonQuery();
Console.WriteLine("Now - Ticks: " + now.Ticks + ", Kind = " + now.Kind);
// Select - DataTable
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM TESTTABLE", sqlConnection);
da.Fill(ds, "testTable");
DataTable dt = ds.Tables["testTable"];
System.DateTime Time1= System.DateTime.MinValue;
System.DateTime Time2 = System.DateTime.MinValue;
System.DateTimeOffset Time3 = System.DateTimeOffset.MinValue;
unsafe
{
Console.WriteLine("Time1/Time2 size: " + sizeof(System.DateTime));
Console.WriteLine("TimeOffset size: " + sizeof(System.DateTimeOffset));
}
foreach (DataRow dr in dt.Rows)
{
int id = Convert.ToInt32(dr["Id"]);
Time1 = (System.DateTime)(dr["Time1"]);
Time2 = (System.DateTime)(dr["Time2"]);
Time3 = (System.DateTimeOffset)(dr["TimeOffset"]);
Console.WriteLine(string.Format("Ticks = {0}, Kind = {1}", Time1.Ticks, Time1.Kind));
Console.WriteLine(string.Format("Ticks = {0}, Kind = {1}", Time2.Ticks, Time2.Kind));
Console.WriteLine(string.Format("Ticks = {0}, Kind = {1}", Time3.Ticks, Time3.Offset));
}
}
실행해 보면, 출력 결과는 다음과 같습니다.
Now - Ticks: 634491664775545133, Kind = Utc
Time1/Time2 size: 8
TimeOffset size: 12
Ticks = 634491664775530000, Kind = Unspecified
Ticks = 634491664775545133, Kind = Unspecified
Ticks = 634491664775545133, Kind = 00:00:00
차이점이 잘 드러나고 있지요!
DateTime, DateTime2가 Timezone에 대한 정보를 보관하고 있지 않기 때문에 만약 이에 대한 민감한 프로그램을 개발하고 있다면 DateTimeOffset으로 지정하는 것이 좋습니다.
DateTime의 경우 DateTime2/DateTimeOffset과의 주요 차이점은 바로 3.33...ms(밀리세컨드) 단위 까지만 데이터를 보관한다는 점이고.
닷넷 개발자 입장에서는 저 정도만 알아두면 개발하는 데 크게 무리는 없겠지만, SQL 서버상의 차이점까지 알아두시면 좀 더 도움이 될 수 있겠지요? ^^
[The ultimate guide to the datetime datatypes에서 인용]
-
DateTime
- 8byte
- 3.33ms 정확성
- 1753-01-01 ~ 9999-12-31일까지의 날짜 표현
-
DateTime2
- 6~8byte
- 100ns 정확성
- 0001-01-01 ~ 9999-12-31 일까지의 날짜 표현
-
DateTimeOffset
- 8~10byte
- 100ns 정확성
- 0001-01-01 ~ 9999-12-31 일까지의 날짜 표현, Timezone 표현 가능
(근데 위의 표를 보니... 한 가지 이해 안되는 것이 있는데요. DateTime과 DateTime2는 점유 공간 면에서 동일하게 8바이트가 소모되는데 어째서 정확성도 떨어지고 표현이 가능한 시작 년도도 1753년으로 해야만 했을까요?)
요즘 들어서, 국제화가 중요해지다 보니 Timezone에 대한 염두를 하지 않을 수 없습니다. 그런데, 이를 고려하기 위해 DateTimeOffset을 사용하게 되면 닷넷 프로그래밍에서 DateTime 자료형이 아니게 되어 처리가 다소 번거로운 단점이 있습니다. 따라서, SQL Server에서는 무조건 데이터를 UTC 형식으로 저장한다는 원칙을 세운다면 이후의 프로그램에서 좀 더 쉽게 접근하는 것이 가능합니다.
그런데, DateTime/DateTime2에 대해서 애당초 Kind 속성을 Unspecified가 아닌 Utc로 SELECT 단계에서부터 반환받고 싶다면 어떻게 해야 할까요?
음... 방법이 없습니다. ^^; 다음과 같이 한번 더 DateTime 타입을 생성해 주는 것으로 부가적인 처리를 해주어야 합니다.
foreach (DataRow dr in dt.Rows)
{
...[생략]...
System.DateTime time2 = (System.DateTime)(dr["Time2"]);
System.DateTime time2_with_zone = new DateTime(time2.Ticks, DateTimeKind.Utc);
...[생략]...
}
여기서 잠깐,
Firebird의 Timestamp도 짚고 넘어가볼까요? ^^
테스트를 위해 간단하게 다음과 같은 형식의 DB/Table을 만들고,
create database 'c:\temp\test.fdb' user 'SYSDBA' password 'masterkey';
commit;
connect 'c:\temp\test.fdb' user 'SYSDBA' password 'masterkey';
CREATE TABLE TESTTABLE (NAME VARCHAR(50) NOT NULL PRIMARY KEY, Created TIMESTAMP NOT NULL);
UTC 형식의 DateTime 값을 넣고 출력하는 예제를 만들어 봅니다.
using (FbConnection fbConnection =
new FbConnection(@"DataSource=localhost;Port=3050;Database=c:\temp\test.fdb;User=SYSDBA;Password=masterkey"))
{
fbConnection.Open();
// Create
FbCommand insertCommand = new FbCommand();
insertCommand.Connection = fbConnection;
insertCommand.CommandText = "INSERT INTO TESTTABLE(NAME, Created) VALUES (@NAME, @Created)";
insertCommand.Parameters.Add("@NAME", FbDbType.VarChar, 50);
insertCommand.Parameters.Add("@Created", FbDbType.TimeStamp);
string nameValue = "Name" + Guid.NewGuid().ToString(); ;
insertCommand.Parameters[0].Value = nameValue;
insertCommand.Parameters[1].Value = DateTime.UtcNow;
int affected = insertCommand.ExecuteNonQuery();
Console.WriteLine("# of affected row: " + affected);
// Select - DataTable
DataSet ds = new DataSet();
FbDataAdapter da = new FbDataAdapter("SELECT * FROM TESTTABLE", fbConnection);
da.Fill(ds, "testTable");
DataTable dt = ds.Tables["testTable"];
foreach (DataRow dr in dt.Rows)
{
string name = dr["NAME"] as string;
DateTime created = Convert.ToDateTime(dr["Created"]);
Console.WriteLine(string.Format("Kind = {0}, Ticks = {1}", created.Kind, created.Ticks));
}
}
실행 후 출력 결과는 다음과 같습니다.
[실행 예]
Kind = Unspecified, Ticks = 634491803025870000
척 보니... SQL 서버의 DateTime 타입과 동일한 결과를 보여주고 있습니다. Timezone에 대한 기록 방법이 없으니, 역시 무조건 UTC 기준으로 시간 관리를 하시는 것이 좋습니다.
첨부된 파일은 위의 코드를 포함한 예제 프로젝트입니다.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]