Paul Liebrand's Weblog

I agree. I also found this MSDN post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3372656&SiteID=1&mode=1 and I submitted some comments and a link to this blog.

in case anyone is interested in what it looks like in the database, use this query (change tp_listid and tp_ID to something that matches your environment):

select datetime1, datetime2, bit1, tp_iscurrentversion, tp_version from alluserdata
where tp_listid = ’49dc3d86-b77d-4d9c-be4f-687a63451ec5′
and tp_ID = ’3′
order by tp_modified DESC

Thanks for the query example. I was able to look my alert up with the tp_listid only, found by looking in my ImmedSubscriptions table.(I’m the only person using the development server here so there is only that one alert)

Is ImmedSubscriptions.UserId the same as AllUserData.tp_ID?

I don’t think so. the tp_ID is the ID of the event item and one way of finding this is by opening the event from your list in the web browser; the ID should show up in the address bar as a parameter on the query string (normally the first one).

the easiest way to find the tp_listID is by going into your list settings (again, in the web browser) and clicking on ‘Information management policy settings’; your listid should be the only parameter on the querystring in the address bar.

I haven’t looked at the alerts in the ImmedSub table (yet).

I don’t think so. the tp_ID is the ID of the event item and one way of finding this is by opening the event from your list in the web browser; the ID should show up in the address bar as a parameter on the query string (normally the first one).

the easiest way to find the tp_listID is by going into your list settings (again, in the web browser) and clicking on ‘Information management policy settings’; your listid should be the only parameter on the querystring in the address bar.

I haven’t looked at the alerts in the ImmedSub table (yet).

I honestly do not think the problem lies with how the data is stored. I believe the problem lies in the code that the job runs. MS is either a) doubling up on the time zone adjustment, or b) they are unnecessarily adjusting the time zone.

I think there is both a problem with how the times are stored in the database (for the initial creation of an all day event) and with the code that generates the alerts.

I know there is an inconsistency in the way the times are stored in the database: if you create an all day event, the times are stored as 12:00AM – 11:59PM, so as local absolute time, not UTC time. if you then go back to the event and uncheck “all day event” but leave the times at 12AM-11:59PM, a new entry (when versioning is turned on) is made but now the times are stored as UTC time.

it seems strange this to that this is done on purpose, looks like a bug to me. but on top of that the alerts don’t interpret the times correctly either.

I think there is both a problem with how the times are stored in the database (for the initial creation of an all day event) and with the code that generates the alerts.

I know there is an inconsistency in the way the times are stored in the database: if you create an all day event, the times are stored as 12:00AM – 11:59PM, so as local absolute time, not UTC time. if you then go back to the event and uncheck “all day event” but leave the times at 12AM-11:59PM, a new entry (when versioning is turned on) is made but now the times are stored as UTC time.

it seems strange this to that this is done on purpose, looks like a bug to me. but on top of that the alerts don’t interpret the times correctly either.

That’s hilarious. I will verify jeroen’s comments about what is actually stored in the db. If he is correct, then they will have several things to fix. I wonder if it’s possible to implement our own fix for this in the mean time…like a stored procedure or perhaps some tweaks to their input form templates…(bear in mind, i wouldn’t know where to begin for the input form templates, but I could take a stab at some sort of stored procedure…)

Cheers

I captured the SQL query using the profiler and this is just calling the ‘proc_AddListItem’ stored procedure. this sp doesn’t do anything with the date/time but just inserts it into the table. take a look at the sql that is executed when I create a new all day event: http://cid-a114391ee07d2109.skydrive.live.com/self.aspx/blogposts/sqlsample_to_insert_new_event_item.txt

when I change this item and uncheck “all day event” but leave times unchanged, the sql that is executed now shows the times in UTC format (and querying the table shows this as well).

I captured the SQL query using the profiler and this is just calling the ‘proc_AddListItem’ stored procedure. this sp doesn’t do anything with the date/time but just inserts it into the table. take a look at the sql that is executed when I create a new all day event: http://cid-a114391ee07d2109.skydrive.live.com/self.aspx/blogposts/sqlsample_to_insert_new_event_item.txt

when I change this item and uncheck “all day event” but leave times unchanged, the sql that is executed now shows the times in UTC format (and querying the table shows this as well).

@Kevin – I am sure Microsoft will fix this issue. However, they will probably claim it has a low business impact and it will land up at the bottom of the bug fix list.

@Jeroen – Interesting… they definitely have some inconsistencies going on.

This exact problem is happening for us with connecting sharepoint calendars to Outlook as well, but only with Outlook 2003. At my site, most users are using 2003, but a few are on 2007. On 2007, the all day events show correctly, but on 2003 they appear to spill over 2 days. If you open the item, it shows the time info as “From 12:00AM to 11:59PM (GMT) Casablanca, Monrovia, Reykjavik”

Hopefully this issue is resolved soon as we are in the middle or rolling this out company-wide, and this feature is going to be used quite often by our users.

Cheers!

Forgot to post my output, here it is below. Also, can someone else try the ‘connect to outlook’ if they have outlook 2003 to see if that bug is also repeatable?

Thanks!!

Gathering Region Information (6/18/2008 4:26:02 PM) …

System Information
==================
Daylight Savings Time Zone: Canada Central Standard Time
Standard Time Zone: Canada Central Standard Time
Universal Time: 6/18/2008 10:26:02 PM
Local Time: 6/18/2008 4:26:02 PM
Offset: -06:00:00

SharePoint Information
======================
Farm Build #: 12.0.0.6219
Web Application Time Zone ID: 36

Root Web
========
Zone ID: 36
Zone Description: (GMT-06:00) Saskatchewan
Universal Time: 6/18/2008 10:26:02 PM

Web
===
Zone ID: 36
Zone Description: (GMT-06:00) Saskatchewan
Universal Time: 6/18/2008 10:26:02 PM

Alert Information
=================
Type Frequency Title
—- ——— —–
All Immediate Calendar

The follow is a count of all alerts in the
specified web grouped by timezone ID.

Zone Count
—- —–
36 1

END

Forgot to post my output, here it is below. Also, can someone else try the ‘connect to outlook’ if they have outlook 2003 to see if that bug is also repeatable?

Thanks!!

Gathering Region Information (6/18/2008 4:26:02 PM) …

System Information
==================
Daylight Savings Time Zone: Canada Central Standard Time
Standard Time Zone: Canada Central Standard Time
Universal Time: 6/18/2008 10:26:02 PM
Local Time: 6/18/2008 4:26:02 PM
Offset: -06:00:00

SharePoint Information
======================
Farm Build #: 12.0.0.6219
Web Application Time Zone ID: 36

Root Web
========
Zone ID: 36
Zone Description: (GMT-06:00) Saskatchewan
Universal Time: 6/18/2008 10:26:02 PM

Web
===
Zone ID: 36
Zone Description: (GMT-06:00) Saskatchewan
Universal Time: 6/18/2008 10:26:02 PM

Alert Information
=================
Type Frequency Title
—- ——— —–
All Immediate Calendar

The follow is a count of all alerts in the
specified web grouped by timezone ID.

Zone Count
—- —–
36 1

END

too was having the same problem with the “ALL Day Events” feature when using Outlook 2003 and connecting my Sharepoint calendar to Outlook 2003. When the sharepoint calendar was viewed in Outlook, the time was also off by minus 5 hrs but did display the correct time in the Outlook calendar after the event was exported to Outlook. I also had the problem of existing shared calendars in Outlook 2003 disappearing when I connected the sharepoint calendar to Outlook. It appears all functionality between sharepoint and Outlook 2003 does not work 100 % correctly but does work correctly when using Outlook 2007. In sharepoint you can share tasks with Outlook 2007 but not Outlook 2003.

I loaded Office 2007 on a test pc and and everything worked correctly in regards to connecting sharepoint calendars to Outlook 2007 without existing shared calendars disappearing and also using the All Day Event feature. Times were correctly displayed when viewing the event via the sharepoint calendar in Outlook 2007. I did not test the Alert feature using the All Day Event but would venture to say that Outlook 2007 would probably resolve this issue also since they’re all related.

I’ve just found this thread after spending all morning replicating time stamp issues. In a nutshell, I can show the same issue but with a twist!

If I create a new calendar item which is an all day event over 2 days, everything on screen shows fine e.g.

Title: Add new
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

I set an alert on this calendar and the initial alert on creation shows:

Title: Add new
Start: 23/08/2008 01:00
End: 25/08/2008 00:59
All Day Event: Yes

If I edit the same item I get the results below:

On screen -

Title: Add new event
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

Emailed alert -

Title: Add new event
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

So without even unchecking the all day alert box, I can get the time to “fix” itself. We are having so many problems relating to dates and times and I have changed the regional settings in as many places as I can. I haven’t had a change to download the app and publish my findings but I thought I’d share my experiences!

I’ve just found this thread after spending all morning replicating time stamp issues. In a nutshell, I can show the same issue but with a twist!

If I create a new calendar item which is an all day event over 2 days, everything on screen shows fine e.g.

Title: Add new
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

I set an alert on this calendar and the initial alert on creation shows:

Title: Add new
Start: 23/08/2008 01:00
End: 25/08/2008 00:59
All Day Event: Yes

If I edit the same item I get the results below:

On screen -

Title: Add new event
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

Emailed alert -

Title: Add new event
Start: 23/08/2008 00:00
End: 24/08/2008 23:59
All Day Event: Yes

So without even unchecking the all day alert box, I can get the time to “fix” itself. We are having so many problems relating to dates and times and I have changed the regional settings in as many places as I can. I haven’t had a change to download the app and publish my findings but I thought I’d share my experiences!

I’ve recently noticed that my email Alerts display an incorrect Start and End Time if the alert is for the creation of an All Day Event.

If you view the item in the site calendar – the time displays correctly. It’s only the text in the email Alert that it is incorrect.

I have checked all the regional setting at hte user level and at the site level, but no luck yet. Came across this post, would be great if anyone or Microsoft can get a quick fix for this issue.

I can verify the same issue on our system (at -4 hours instead), but don’t have the authority to run the utility on our servers to give the output. In case MS is interested, we use it for scheduling Time Off, and so it really impacts us as the days actually shift.

Affects about 250 users here.

I can verify the same issue on our system (at -4 hours instead), but don’t have the authority to run the utility on our servers to give the output. In case MS is interested, we use it for scheduling Time Off, and so it really impacts us as the days actually shift.

Affects about 250 users here.

Any resolution to this issue. We use Outlook 2007 and created some recurring events in outlook and copied them to SharePoint within Outlook. We discovered that SharePoint was adding a day onto the occurence. It seemed to be right if we used number of days rather than an end by date.

Also added a one time recurring event from 12:30am to 11:30pm (23 hours) on 10/15/2008. in outlook. In SharePoint it shows as: 10/15/2008 12:30 AM 10/14/2008 11:30 PM which means it does not show.

Sure sounds like a bug to me.

We are experiencing the same issue with alerts being offset incorrectly. Is anyone aware of where MS stands on this? Is there a bug logged of which we can check the status?

We are experiencing the same issue with alerts being offset incorrectly. Is anyone aware of where MS stands on this? Is there a bug logged of which we can check the status?

I’m experiencing the same offset problems with not only the calendar alerts but for document library alerts as well.

Another problem, probably somehow connected, and, at least for my company – serious business impact…

Try to create a custom Date or Date/Time column in wss3.0 built-in calendar list. Create new event, set start date, end date, Your custom date and check the all-day event checkbox. Edit Your event few times. Every time You edit the event, Your custom date field decreases or increases a day off if it’s a Date field, or an hour off, if it’s Date/Time field. Looks quite messy and i’m not going to believe it’s a feature.

The problem exists only for wss3.0 time zone setting other than Greenwich time. If You are in time zone GMT+ Your custom fields entries decreses, GMT- -> increases.

My configuration:
Windows server 2003 r2
WSS 3.0
WindowsIntDatabase

Tried clean WSS3.0, WSS3.0 +sp1 and WSS3.0+sp1+december update -> same things happen.