0

I am planning to partition a large table on [datetimeoffset]. I thought the data in that column were all yyyy-mm-dd hh:mm:ss -08:00, but there are multiple offsets in that column such as -07:00 and -06:00.

My question is what should my partition function use? This is what I'm currently using...

CREATE PARTITION FUNCTION [PF_table_DatetimeOffset0] (DATETIMEOFFSET(0))
AS
RANGE RIGHT FOR VALUES (
    '20120101 00:00:00.000 -08:00', 
    '20130101 00:00:00.000 -08:00',  
    '20140101 00:00:00.000 -08:00',
    '20150101 00:00:00.000 -08:00',
    '20150201 00:00:00.000 -08:00',
    '20150301 00:00:00.000 -08:00',
    '20150401 00:00:00.000 -08:00',
    '20150501 00:00:00.000 -08:00',
    '20150601 00:00:00.000 -08:00',
    '20150701 00:00:00.000 -08:00',
    '20150801 00:00:00.000 -08:00',  
    '20150901 00:00:00.000 -08:00',  
    '20151001 00:00:00.000 -08:00',  
    '20151101 00:00:00.000 -08:00',  
    '20151201 00:00:00.000 -08:00',  
    '20160101 00:00:00.000 -08:00'
);

Thank you!

2 Answers 2

3

Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year and Check this Link

1

No one can tell you what boundaries your function should use but we can explain the behavior.

Partition function timezoneoffset values are compared according to normal rules for comparing temporal data in SQL Server. The value '2012-01-01T00:00:00.000 -08:00' is equal to both '2012-01-01T01:00:00.000 -07:00' and '2012-01-01T02:00:00.000 -06:00'. Even though the time zones are different, rows with these values will all be stored in the same partition because they represent the exact same date/time values.

Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year. If one's objective is to keep all of the data for a given month in the same partition based on local time, the partition boundary timezone offset must be adjusted accordingly. For example, the offset "-08:00" represents midnight Pacific time when Daylight Savings time is observed in the US. But when clocks are changed back one hour to revert to Pacific Standard time, the offset for midnight becomes "-07:00". If the offset is not changed for the monthly boundary following the time change, one hour of rows will be stored in a different monthly partition than the one intended. This will continue until the boundary after next time change.

Below is a partition function example to store monthly data based on Pacific Time with DST rules.

CREATE PARTITION FUNCTION [PF_table_DatetimeOffset0] (DATETIMEOFFSET(0))
AS
RANGE RIGHT FOR VALUES (
    '20120101 00:00:00.000 -07:00', 
    '20130101 00:00:00.000 -07:00',  
    '20140101 00:00:00.000 -07:00',
    '20150101 00:00:00.000 -07:00',
    '20150201 00:00:00.000 -07:00',
    '20150301 00:00:00.000 -07:00', 
    '20150401 00:00:00.000 -08:00', --Pacific Daylight Savings Time began 02:00:00 on 2015-03-08
    '20150501 00:00:00.000 -08:00',
    '20150601 00:00:00.000 -08:00',
    '20150701 00:00:00.000 -08:00',
    '20150801 00:00:00.000 -08:00',  
    '20150901 00:00:00.000 -08:00',  
    '20151001 00:00:00.000 -08:00',  
    '20151101 00:00:00.000 -08:00', 
    '20151201 00:00:00.000 -07:00', --Pacific Standard Time began 02:00:00 on 2015-11-01 
    '20160101 00:00:00.000 -07:00'
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.