GMT(UTC) date Storage Retrieval Questions

I'm planning on converting all my database times to UTC.

I've have all the procedures set up for converting LocalUTC
and UTCLocal.

One potential issue that comes to mind is what I call "Date RollUp"

That is the time period at UTC time for a date, which will break differently 
than at the users TimeZone (e.g. Central Standard Time) when rolling up 
"dependent" data because of the time (hh:mm:ss) the data was originally time 
stamped (e.g. Central Standard Time).  This may have
an effect on dependent field values.  For example if you are taking sales 
and you want the total sales that occurred during "a" day, the value 
returned may be different based on how the query is constructed.

Another issue is all the date conversions (to and from the DB as well as any 
date checks, queries, etc) that need to take place which "May" significantly 
slow down processing.

Anyone had any experience storing date/times in UTC and offer any 
suggestions or pitfalls of concern?


0
David
1/18/2010 2:06:11 PM
access 16762 articles. 2 followers. Follow

4 Replies
736 Views

Similar Articles

[PageSpeed] 25

I suggest you use two fields:
- a Date/Time to store the value in whatever suits (e.g. UTC.)
- an Integer field to store the number of minutes offset (plus or minus) 
from your standard.

This arrangement:
a) Allows a very simple and efficient query manipulation, e.g.:
    LocalDateTime: DateAdd("n", [MinutesOffset], [UTCDateTime])

b) Copes with all situations, including non-standard daylight savings 
changes. For example, Sydney had different daylight saving rules for 2000 
than for other years (due to holding the Olympic Games), and my city (Perth) 
has no daylight saving at all this year even though it did for the 3 
previous years. (Minutes offset is the most efficient solution for 
fractional hour zones.)

c) Still only requires the user to enter a single date/time value for any 
record. They only need to assign their offset once, and you can assign it as 
a Default Value or in Form_BeforeInsert. (You can give them an unbound text 
box to enter the local date/time, and use its AfterUpdate event to calculate 
and store the UTC value.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"David" <dw85745NOT@earthlink.net> wrote in message 
news:#r7FkdEmKHA.1824@TK2MSFTNGP04.phx.gbl...
> I'm planning on converting all my database times to UTC.
>
> I've have all the procedures set up for converting LocalUTC
> and UTCLocal.
>
> One potential issue that comes to mind is what I call "Date RollUp"
>
> That is the time period at UTC time for a date, which will break 
> differently than at the users TimeZone (e.g. Central Standard Time) when 
> rolling up "dependent" data because of the time (hh:mm:ss) the data was 
> originally time stamped (e.g. Central Standard Time).  This may have
> an effect on dependent field values.  For example if you are taking sales 
> and you want the total sales that occurred during "a" day, the value 
> returned may be different based on how the query is constructed.
>
> Another issue is all the date conversions (to and from the DB as well as 
> any date checks, queries, etc) that need to take place which "May" 
> significantly slow down processing.
>
> Anyone had any experience storing date/times in UTC and offer any 
> suggestions or pitfalls of concern? 

0
Allen
1/18/2010 2:38:46 PM
selam�naleyk�m

hello

"David" <dw85745NOT@earthlink.net>, iletide �unu yazd� 
news:#r7FkdEmKHA.1824@TK2MSFTNGP04.phx.gbl...
> I'm planning on converting all my database times to UTC.
>
> I've have all the procedures set up for converting LocalUTC
> and UTCLocal.
>
> One potential issue that comes to mind is what I call "Date RollUp"
>
> That is the time period at UTC time for a date, which will break 
> differently than at the users TimeZone (e.g. Central Standard Time) when 
> rolling up "dependent" data because of the time (hh:mm:ss) the data was 
> originally time stamped (e.g. Central Standard Time).  This may have
> an effect on dependent field values.  For example if you are taking sales 
> and you want the total sales that occurred during "a" day, the value 
> returned may be different based on how the query is constructed.
>
> Another issue is all the date conversions (to and from the DB as well as 
> any date checks, queries, etc) that need to take place which "May" 
> significantly slow down processing.
>
> Anyone had any experience storing date/times in UTC and offer any 
> suggestions or pitfalls of concern?
>
>
>
> __________ ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan sa�lanan 
> bilgiler, vir�s imza veritaban� s�r�m�: 4783 (20100118) __________
>
> �leti ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan denetlendi.
>
> http://www.nod32.com.tr
>
>
> 

__________ ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan sa�lanan bilgiler, vir�s imza veritaban� s�r�m�: 4783 (20100118) __________

�leti ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan denetlendi.

http://www.nod32.com.tr



0
ahmet
1/18/2010 2:58:59 PM
Mr. Browne:

Thanks for response.

As you suggested, I had previously tried an offset and DateAdd for test.
purposes as one of my primary concerns is the amount of time it takes to 
convert back and forth between the two (UTC other TZ).  Seems like the 
offset may be the most efficient.

Any experience with "rollups" and returning different values because of 
date/time break differences --especially--where historical data already 
exists?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:%23u6vyvEmKHA.5040@TK2MSFTNGP06.phx.gbl...
>I suggest you use two fields:
> - a Date/Time to store the value in whatever suits (e.g. UTC.)
> - an Integer field to store the number of minutes offset (plus or minus) 
> from your standard.
>
> This arrangement:
> a) Allows a very simple and efficient query manipulation, e.g.:
>    LocalDateTime: DateAdd("n", [MinutesOffset], [UTCDateTime])
>
> b) Copes with all situations, including non-standard daylight savings 
> changes. For example, Sydney had different daylight saving rules for 2000 
> than for other years (due to holding the Olympic Games), and my city 
> (Perth) has no daylight saving at all this year even though it did for the 
> 3 previous years. (Minutes offset is the most efficient solution for 
> fractional hour zones.)
>
> c) Still only requires the user to enter a single date/time value for any 
> record. They only need to assign their offset once, and you can assign it 
> as a Default Value or in Form_BeforeInsert. (You can give them an unbound 
> text box to enter the local date/time, and use its AfterUpdate event to 
> calculate and store the UTC value.)
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "David" <dw85745NOT@earthlink.net> wrote in message 
> news:#r7FkdEmKHA.1824@TK2MSFTNGP04.phx.gbl...
>> I'm planning on converting all my database times to UTC.
>>
>> I've have all the procedures set up for converting LocalUTC
>> and UTCLocal.
>>
>> One potential issue that comes to mind is what I call "Date RollUp"
>>
>> That is the time period at UTC time for a date, which will break 
>> differently than at the users TimeZone (e.g. Central Standard Time) when 
>> rolling up "dependent" data because of the time (hh:mm:ss) the data was 
>> originally time stamped (e.g. Central Standard Time).  This may have
>> an effect on dependent field values.  For example if you are taking sales 
>> and you want the total sales that occurred during "a" day, the value 
>> returned may be different based on how the query is constructed.
>>
>> Another issue is all the date conversions (to and from the DB as well as 
>> any date checks, queries, etc) that need to take place which "May" 
>> significantly slow down processing.
>>
>> Anyone had any experience storing date/times in UTC and offer any 
>> suggestions or pitfalls of concern?
> 


0
David
1/18/2010 3:02:53 PM
"David" <dw85745NOT@earthlink.net> wrote in message 
news:#vHrR9EmKHA.2680@TK2MSFTNGP04.phx.gbl...
>
> Any experience with "rollups" and returning different values because of 
> date/time break differences --especially--where historical data already 
> exists?

No. Others with experience here might contribute.

If you have lots of existing data, and you don't want to convert it, or most 
calculations depend on the local time, you could go the other way: store the 
local time, and the offset to UTC/GMT.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org. 

0
Allen
1/19/2010 1:47:33 AM
Reply:

Similar Artilces:

Outlook Connector Contacts Priority Question
What takes priority when you sync your Local outlook contact information with that of the information in your Windows Live account, the local contact information or the information on the Live account? There is nothing that I can find that speaks to this very important issue. Also, when does the information get synced other than the first time you install the connector software. I am using Outlook 03 SP3 on XP SP2. it syncs on a schedule, based on the Send and receive settings. The sync is both ways but the server takes precedence based on my understanding. -- Diane Poremsky [M...

Date updates from worksheet to chart & changes date to a date series! Help!!
Hi, I have a 2 sheet workbook, one sheet has my data - Date Kg's 1/5/2003 91.0 2/8/2003 89.1 5/31/2003 90.1 8/21/2003 89.5 1/18/2004 89.9 2/2/2004 88.0 3/6/2004 88.1 4/17/2004 89.2 9/13/2004 88.5 10/4/2004 86.4 11/8/2004 87.3 12/21/2004 88.0 The second sheet has my chart. When the date is carried over to the chart, the dates are changed to a series of dates, starting 01/05/2003, 02/05/2003 etc... The cells are formated to UK date on both the data and the chart. Any suggestions? Thanks Jamie JayJg, One option would be to reference the cells containing your dates with f...

Howe to stop storage groups
Hi there, for a copy-procedure I have to dismount special storage groups. if i want to stop the information store I can do this by: net stop .... But how can dismount and mount a store without stopping the whole information store?` thx in adv brgds JB -- FPA-Haag Webportal & Support J�rg Becker Sch�tzenstrasse 7 49838 Lengerich 05904 - 964141 Exchange Server 200x - right-click on each Store - Dismount Store. -- Bharat Suneja MCSE, MCT -------------------------------- "J�rg Becker" <support@fpa-haag.de> wrote in message news:eUslePSxFHA.3256@TK2MSFTNGP09.phx.g...

GMT date & time
Hi Can someone tell me how to save the current GMT date & time into a cell? The reason I use GMT is we have daylight saving here and I would like the time to be independent of daylight saving. mike wrote: > Hi > Can someone tell me how to save the current GMT date & time into a > cell? The reason I use GMT is we have daylight saving here and I > would like the time to be independent of daylight saving. See http://www.cpearson.com/excel/timezone.htm for details of how to get UTC Hi Mike, First you need to know your timezone and variation from GMT, a google search...

PDF conversion question
I recently had to replace my computer and printer. My new computer has Windows 7 which I am slowly getting used to. I installed my old Office 2003 program on the compuer. At some time in the past I was able to change my printer settings to allow pdf conversion and printing of Word documents. Unfortunately I do not remember how I did this or where I got the instructions. I need to do the same on my new system. As far as I can tell Windows 7 didn't come with Adobe Acrobat and I think I have to have it in order to convert to pdf. Is it still a free download? Can someon...

Convert Dates into four digit YEAR ONLY
I have a database of 20,000 transactions, each of which contains a date. I want to create a pivot table which summarizes the dollar value of the transactions by year. Problem is that the pivot table looks at the date and doesnt see a year--it sees day, month and year, so it will not group by year. I have tried to re-format the date to just a 4 digit year, this does not work. I have tried cutting and "paste special" - values only, and this doesn't work-- Excel ALWAYS sees the Julian number, not the year. Help! If your dates are in "Julian" format, e.g., 2005...

[in horz.bar+chart] Disply dates on value axis?
Something about selcting - 'date option'. But, where, how? -- Boswell You can only select the time scale option for a category axis. This is the axis that the bars grow out of. If you need dates on the value axis, you can simply select a date number format, and provide numerical dates for the Y values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Boswell" <boswell6@sbcglobal.net> wrote in message news:01A192F9-6684-4B2A-815F-1F6C65528B4F@microsoft.com... > Something about selcting - 'date o...

input a date or update it based on date in another cell
-Someone asked me to setup a spreadsheet like this cell b2 is entered as a date cell c2 is entered as a number cell d2 is a calculated date =b2+c2 now cell c2 gets more interesting. the user will enter a date like they did in b2, but if the number in c2 is changed modifing the date in d2 they want the date in c3 to be advanced as well. They want to use it to track some projects using software they have already paid for and not buy additional software. -- Doug P You don't explain how the date in c3 is affected by the change in c2. If c2 changes from 2 to 5 do you want cell c3 to b...

Pivot date grouping
I have a spreadsheet that has been obtained from a external database query. I am now trying to group the dates into months and years, however no luck. If I save the worksheet as a csv file and the back into an excel file the date grouping works fine. This however does not solve my problem. hel please When you get the data from the query, the dates may be imported as text, which would prevent them from being grouped. There are instructions here for converting the text dates to real dates: http://www.contextures.com/xlDataEntry03.html sue wrote: > I have a spreadsheet that has be...

Want to get rid of UTC offset in serialization of DateTime
Hi everyone! In the following code we get a UTC offset in xml. Since I want my WAP users in UTC+02:00 to see data on a server in some US time zone, a lot of confusion will be created from this behavior. Either I use .NET logic on the server and get server local time or I create xhtml pages directly from serialized objects. Eitherway I'll have to write my own logic to take care of utc offset. Is it possible to override XmlSerializer so what my local desktop user send to the webservice is saved on the server without utc offset? That way, the time that is local to my users will be saved on ...

Concatenate 2 columns date & time
Hi, I am working with Excel 2003 and have a ws that I need to combine column B which is a date, with column C which contains the time. No matter how I have formatted column D, I cannot get the date to display correctly. What I have is: B C 05/01/2010 10:55 24/12/2009 09:35 I need column D to show as: 05/01/2010 10:55 24/12/2009 09:35 I have tried several different formulas/formatting in Column D but always end u...

the resources available for the selected dates or times do not mee
the resources available for the selected dates or times do not meet the minimum resources for this service. I have set up the service with -- Robert D ...

Question on Sorting by (date, etc)
I have a column (A) which sre dates. If I select the column heading and click either the sort ascending or desending button, the date column alone sorts independent of the other eight columns that comprise the entire entry. How would I go about being able to sort by a particular column and have everything sort with it? I have other columns that I would like to sort by sometimes too, like "color", "type", etc. Thanks for reading! select all the columns and now go to data | sort . On Feb 20, 11:21=A0pm, "Ed" <2...@333.com> wrote: > I have a column...

ERROR check if a date set entered violates a previously entered date set
I am devloping an Excel program that has a list of date sets, not necessarily in sequencial order. What I want is an ERROR CHECK if a date set violates any date set previously entered. What I was looking for was that a date set could not be typed in as follows: 1/10/04 to 4/20/04 2/10/04 to 3/20/04 The second date set falls within the first set and should be invalid setting off a message or an alert of an invalid entry. I am interested in making it idiot proof so that one could not enter date sets within or covering dates sets already entered. This one really throws me, Please help. Buzz, Ar...

Getting the current date/time
Hello, I'm trying to access a date field of a particular database and the code generated by MFC defines the date variable as "DATE". I believe this DATE referes to COleDateTime and I don't know how to get the current date/time to populate this field. I'm tried the following already, but none of these worked: ----------- Example 1 ----------- DATE test; test = COleDateTime::GetCurrentTime() --------------------------------- VC says that COleDateTime is not defined. ----------- Example 2 ----------- DATE test; test = ::GetCurrentTime() -------------------------------...

Policy Question
I am running Exchange 2003. My question is: Can I schedule Recipient policies to run once a week, every other week or monthly? I do not see the capability to schedule when they would run during their creation. So, it looks like it's an every night thing. I have told my CIO 'no, I can't schedule when they run at the moment' but thought I'd ask the question. Thanks for any Replies - Jon On Tue, 10 Apr 2007 09:47:11 -0500, "Jon Slater" <lanmanjs@comcast.net> wrote: > I am running Exchange 2003. My question...

Trying to make cell "date sensitive" to specific date
How do I make a cell "date sensitive" to calculate a formula or input a value on a specific date? I suppose something like: =IF(TODAY()="1/18/2005", <your formula here>,"") In article <811D02E7-FAF6-43C8-A723-ABAD20EF39B2@microsoft.com>, "ebuzz13" <ebuzz13@discussions.microsoft.com> wrote: > How do I make a cell "date sensitive" to calculate a formula or input a value > on a specific date? ...

Charting dates
I would like to display the dates I have entered in two columns as a graphical representation each month. (bar chart etc) Could someone help? Specifically, I have two columns, one for start date and one for end date. I would like a colored bar for each entry from start to end so I can look at the month and see conflicts graphically TI Kev Jon Peltier's site has links to several sources of instructions for creating a Gantt (timeline) chart. http://www.geocities.com/jonpeltier/Excel/Charts/GanttLinks.html Kev wrote: > I would like to display the dates I have entered in two col...

Date,Time Problem
HI!! We take the difference of two Date/Time Fields having Custom format and Values like this Start Date End Date Cycle Time 2/24/03 16:30 3/25/03 10:11 689:41:41 m/d/yy h:mm m/d/yy h:mm [h]:mm:ss Now I want this Time to be taken as absolute Number expressing it as Hours/Minutes, so that I can take such values for further analysis. But as such it is not possible because Excel always show �Cycle time� value (689:41:41) as �28/01/1900 17:41:41�, and does not yield to any arithmetic operations. H...

Field question
I looked for a standard field which might be called "company activity" meaning what business is the company involved. Strange that this is missing. Before I make a custom field perhaps someone would confirm? Beemer What program are you talking about? If Outlook, what version? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Beemer asked: | I looked for ...

Storage Limit Changes
We have storage limits set for the information store, but have excluded some users. One such user filled his mailbox by forwarding every message from another user to their inbox. So, to get him going until he can delete some mail, we upped his Storage Limit, but it did not seem to take effect right away. Are these properties of the mailbox, or policies that are applied on an interval? Simpler question: Do changes to individual mailbox storage limits take effect immediately, or do we have to wait for something to run that processes these? Applies to e2003 as well ( w/o the required ...

Creating a formula to a cell containing a date
I have two worksheets. One is an input worksheet. The other has formula that refer from the input worksheet. My problem is when I copy a date cell from the input sheet to the other sheet the 2nd sheet will have date of 1/1/99 when there is nothing inputed in the first sheet. I there a way to have nothing show up if nothing is inputted in the inpu sheet and also have the formula -- hb247 ----------------------------------------------------------------------- hb2474's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=418 View this thread: http://www.excelforum.com/s...

Calculating dates based on two dates from 24 hours
I have two dates in my database. I need to start to write and track a report from the second date 24 hours after the first date. I need to report on the time frame it takes to return packets. They are supposed to be returned in a 24 hour period. Hi There is a really good funtion here (just tried them both and I personally like the 2nd one as it gives a clearer result) http://office2010.microsoft.com/en-gb/access-help/on-time-and-how-much-has-elapsed-HA001110218.aspx?CTT=1&origin=EC001022701 just use dateadd to add 1 day to your first time/date and then call the funti...

Macro for Autofiltering certain dates
Code below: Summary per month is in column A, the month and year are in columns B and C respectively. in row 1 (columns H and I) I have the =DATE function to say what month it is. Using these variables I wish to autofilter the A:C column data for the latest month so I can present the latest column A number. I need the data criteria to check the latest A:C data has been imported for that month. Sub Macro3() ' Dim Retail Dim Retailtest Set Retail = Workbooks.Open("N:\mis\EXCEL\EoM\DW-Reports \Retailfigure.csv") Set Retailtest = Workbooks.Open("N:\mis\EX...

CRM V3 Registration Questions
We are preparing to upgrade to CRM V3. I have set up a test environment with V3 to make sure all our customtizations work and to get used to the changes before rolling it out to the masses. I see that V3 has to be registered within 30 days of install. If i register the software on my test environment, will that prevent me from installing/registering on my production environment? (similar to an XP registration) If it does what are people doing for test environements? I really dont want to have to ghost the machine and load it back every 30 days to do development for in house stuff. ...