Solved File Property Time Zone Compensation

May 1, 2012 at 19:13:55
Specs: Windows Server 2003 R2, Athlon XP dual core
I have written (well, edited) a .vbs script to grab the date taken property from a photo. The script then creates a folder from the date, and renames the file by the time the picture was taken. It seems that my script is creating folders and renaming files based on UTC, and I am in EST. I could write the script to subtract 4, but at some point it will be an offset of 5 due to daylight savings time ending. So, rather than writing something creative to determine if we're in daylight savings or not, how could I best modify the value from the file to compensate for daylight savings?

Set objRecordSet = CreateObject("ADODB.Recordset")
dtmPhotoDate = objRecordset.Fields.Item("System.Photo.DateTaken")
strDay = Day(dtmPhotoDate)
strMonth = Month(dtmPhotoDate)
strYear = Year(dtmPhotoDate)
strHour = Hour(dtmPhotoDate)
strMinute = Minute(dtmphotoDate)
strSec = Second(dtmPhotoDate)


See More: File Property Time Zone Compensation

Report •

✔ Best Answer
May 14, 2012 at 14:21:38
Error messages are helpful! That error is WBEM_E_INVALID_OBJECT_PATH, so the instance name of Win32_OperatingSystem is different. You can replace that line with the following:
For Each OS In GetObject("winmgmts:Win32_OperatingSystem").Instances_
  timeZone =  OS.CurrentTimeZone
Next 'os

debugging seems to find a problem with the new stuff, while the old worked for me
Debugging typically finds problems with the new stuff; that's kind of the point. Debugging finds fewer problems in the old stuff, because it has already gone though debugging.

How To Ask Questions The Smart Way



#1
May 2, 2012 at 06:56:27
Do you know what this post is missing?
The rest of the script.

How To Ask Questions The Smart Way


Report •

#2
May 2, 2012 at 08:27:20
Sorry. I assumed the rest of the script was irrelevant to the specific question I was asking. Posting that now. Btw, your link (http://www.catb.org/esr/faqs/smart-questions.html) is dead for me.

On Error Resume Next

'Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT System.ItemPathDisplay, System.Photo.DateTaken FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'C:\Documents and Settings\Administrator\My Documents\Dropbox\DCIM\100MEDIA'", objConnection

'Start at the Beginning of the RecordSet
objRecordSet.MoveFirst

Do Until objRecordset.EOF
strOrigFilePath = objRecordset.Fields.Item("System.ItemPathDisplay")
strFilePath = objRecordset.Fields.Item("System.ItemFolderPathDisplay")
temp = Split(strOrigFilePath, ".")
newtemp = Split(temp(0), "\")
strFileName = newtemp(7)
strFileExt = temp(1)


' 'Extract "Date Taken" info:
dtmPhotoDate = objRecordset.Fields.Item("System.Photo.DateTaken")
strDay = Day(dtmPhotoDate)
strMonth = Month(dtmPhotoDate)
strYear = Year(dtmPhotoDate)
strHour = Hour(dtmPhotoDate)
strMinute = Minute(dtmphotoDate)
strSec = Second(dtmPhotoDate)

' 'Update Formatting to two characters if necessary:
If Len(strDay) = 1 Then strDay = "0" & strDay
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strHour) = 1 Then strHour = "0" & strHour
If Len(strMinute) = 1 Then strMinute = "0" & strMinute
If Len(strSec) = 1 Then strSec = "0" & strSec

strDirectory = strFilePath & strYear & "-" & strMonth & "-" & strDay

If strYear <> "" Then
objRecordset.Fields.Item("System.DateCreated") = dtmPhotoDate
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
End If
strNewFileAndPath = strFilePath & strYear & "-" & strMonth & "-" & strDay & "\" & _
strHour & "h" & strMinute & "m" & strSec & "s" & "." & strFileExt
objFSO.MoveFile strOrigFilePath , strNewFileAndPath
End If
' 'Next file in the RecordSet:
objRecordset.MoveNext
Loop


Report •

#3
May 2, 2012 at 08:51:54
Around the Set objRecordSet = CreateObject("ADODB.Recordset") line, add this line:
timeZone = GetObject("winmgmts:Win32_OperatingSystem=@").CurrentTimeZone
Then change dtmPhotoDate = objRecordset.Fields.Item("System.Photo.DateTaken") to:
dtmPhotoDate = DateAdd("n", timeZone, objRecordset("System.Photo.DateTaken"))

Re: Link: Yeah, I don't control it. If the site doesn't come back in a week-ish, I'll probably just remove it.

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
May 2, 2012 at 10:22:53
I plugged in your line to set the timeZone value and then updated the dtmPhotoDate line as indicated. It did not change the outcome of my script. I added a WScript.Echo to display the value for timeZone and it comes up blank?

Edit: a little more research shows me that .CurrentTimeZone is in fact in minutes, so that part makes sense to me now.


Report •

#5
May 2, 2012 at 10:42:54
the value for timeZone and it comes up blank?
.CurrentTimeZone is in fact in minutes
So is it blank or not?

How To Ask Questions The Smart Way


Report •

#6
May 2, 2012 at 10:53:28
I meant MSDN indicates that the value for .CurrentTimeZone is in minutes. I had previously asked if minutes was correct ("n"), and then saw that it was.

The value is blank in the popup that I added. Here's the latest updated script:

On Error Resume Next

'Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
timeZone = GetObject("winmgmts:Win32_OperatingSystem=@").CurrentTimeZone

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT System.ItemPathDisplay, System.Photo.DateTaken FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'C:

\Documents and Settings\Administrator\My Documents\Dropbox\DCIM\100MEDIA'", objConnection

'Start at the Beginning of the RecordSet
objRecordSet.MoveFirst

Do Until objRecordset.EOF
strOrigFilePath = objRecordset.Fields.Item("System.ItemPathDisplay")
strFilePath = objRecordset.Fields.Item("System.ItemFolderPathDisplay")
temp = Split(strOrigFilePath, ".")
newtemp = Split(temp(0), "\")
strFileName = newtemp(7)
strFileExt = temp(1)


' 'Extract "Date Taken" info:
dtmPhotoDate = DateAdd("h", timeZone, objRecordset("System.Photo.DateTaken"))
WScript.Echo "timeZone is " & timeZone
strDay = Day(dtmPhotoDate)
strMonth = Month(dtmPhotoDate)
strYear = Year(dtmPhotoDate)
strHour = Hour(dtmPhotoDate)
strMinute = Minute(dtmphotoDate)
strSec = Second(dtmPhotoDate)

' 'Update Formatting to two characters if necessary:
If Len(strDay) = 1 Then strDay = "0" & strDay
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strHour) = 1 Then strHour = "0" & strHour
If Len(strMinute) = 1 Then strMinute = "0" & strMinute
If Len(strSec) = 1 Then strSec = "0" & strSec

strDirectory = strFilePath & strYear & "-" & strMonth & "-" & strDay

If strYear <> "" Then
objRecordset.Fields.Item("System.DateCreated") = dtmPhotoDate
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
End If
strNewFileAndPath = strFilePath & strYear & "-" & strMonth & "-" & strDay & "\" & _
strHour & "h" & strMinute & "m" & strSec & "s" & "." & strFileExt
objFSO.MoveFile strOrigFilePath , strNewFileAndPath
End If
' 'Next file in the RecordSet:
objRecordset.MoveNext
Loop


Report •

#7
May 2, 2012 at 11:25:39
Okay, I decided to run your script, sans On Error Resume Next because that's a line you add at the very end of the debug, if at all. I got back a lot of errors, so you'll probably want to address them first. That should also tell you why "timeZone" is blank when it isn't for me.

I also notice you changed the DateAdd to "h" instead of "n", so I suggest you revert that.

How To Ask Questions The Smart Way


Report •

#8
May 2, 2012 at 11:48:54
On Error Resume Next was a part of the original script I copied. Will remove that and debug from there. I did notice that I forgot to change that back to "n" before pasting the code here. I have fixed that.

Will report back after I try this out. Thanks for the help!


Report •

#9
May 3, 2012 at 13:46:23
On another note, the link is working fine for myself, its worth a read.

mike


Report •

#10
May 14, 2012 at 13:31:37
Had a few things come up to distract me, but I finally got a chance to work with this. When I debug the script (latest and greatest posted, with "n" and with the lines you've recommended I add), the first error I get is 8004103A on line 7, which is "timeZone = GetObject("winmgmts:Win32_OperatingSystem=@").CurrentTimeZone". Did I not copy/paste this correctly, or does this perhaps not work with Windows Server 2003 R2?

I will debug from the original script now, but I wanted to note that debugging seems to find a problem with the new stuff, while the old worked for me (minus the extra feature of time zone adjustment).


Report •

#11
May 14, 2012 at 14:21:38
✔ Best Answer
Error messages are helpful! That error is WBEM_E_INVALID_OBJECT_PATH, so the instance name of Win32_OperatingSystem is different. You can replace that line with the following:
For Each OS In GetObject("winmgmts:Win32_OperatingSystem").Instances_
  timeZone =  OS.CurrentTimeZone
Next 'os

debugging seems to find a problem with the new stuff, while the old worked for me
Debugging typically finds problems with the new stuff; that's kind of the point. Debugging finds fewer problems in the old stuff, because it has already gone though debugging.

How To Ask Questions The Smart Way


Report •

#12
May 14, 2012 at 15:47:48
I actually did not originally get an error message, as I was editing the script in Notepad and running the service to execute the script. I now have a prog called VBSEdit installed... I figured since I'm committing more to this script, i'll start getting a better tool for the job. As for debugging finding issues, I guess I was assuming the new code was something known to work, and I was only expecting to see the 'lot of errors' in my original you had mentioned.

That said, I've determined that my script doesn't like System.ItemFolderPathDisplay, despite it being listed in MSDN. I'm curious why it's happy with System.ItemPathDisplay yet balks at this, but I can work around that. The other error I got was trying to access temp(1) when there was no extension (so the array "temp" was only one element). These appear to be fixed, and I have added the change you prescribed.

Here is the script I'm working with right now:

'Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
For Each OS In GetObject("winmgmts:Win32_OperatingSystem").Instances_
timeZone = OS.CurrentTimeZone
Next 'os

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT System.ItemPathDisplay, System.Photo.DateTaken FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'C:\Documents and Settings\Administrator\My Documents\Dropbox\DCIM\100MEDIA'", objConnection

'Start at the Beginning of the RecordSet
objRecordSet.MoveFirst

Do Until objRecordset.EOF
strOrigFilePath = objRecordset.Fields.Item("System.ItemPathDisplay")
temp = Split(strOrigFilePath, ".") 'Separate file path (temp(0)) from extension (temp(1))
If UBound(temp) <1 then
Exit Do 'If there is no extension, skip to next file
End If
newtemp = Split(temp(0), "\")' Separate file path into folder list array
strFilePath = newtemp(0) & "\" & newtemp(1) & "\" & newtemp(2) & "\" & newtemp(3) & "\" & newtemp(4) & "\" & newtemp(5) & "\" & newtemp(6)
strFileName = newtemp(7)
strFileExt = temp(1)

' Extract "Date Taken" info:
dtmPhotoDate = DateAdd("n", timeZone, objRecordset("System.Photo.DateTaken"))
' WScript.Echo "timeZone is " & timeZone
strDay = Day(dtmPhotoDate)
strMonth = Month(dtmPhotoDate)
strYear = Year(dtmPhotoDate)
strHour = Hour(dtmPhotoDate)
strMinute = Minute(dtmPhotoDate)
strSec = Second(dtmPhotoDate)

' Update Formatting to two characters if necessary:
If Len(strDay) = 1 Then strDay = "0" & strDay
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strHour) = 1 Then strHour = "0" & strHour
If Len(strMinute) = 1 Then strMinute = "0" & strMinute
If Len(strSec) = 1 Then strSec = "0" & strSec

strDirectory = strFilePath & strYear & "-" & strMonth & "-" & strDay

If strYear <> "" Then
objRecordset.Fields.Item("System.DateCreated") = dtmPhotoDate
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
End If
strNewFileAndPath = strFilePath & strYear & "-" & strMonth & "-" & strDay & "\" & _
strHour & "h" & strMinute & "m" & strSec & "s" & "." & strFileExt
objFSO.MoveFile strOrigFilePath , strNewFileAndPath
End If
' Next file in the RecordSet:
objRecordset.MoveNext
Loop

This approach appears to take my current offset from UTC and apply it to the timestamp on each file. Does this mean that it will likely be incorrect (an hour off) for pictures taken, for example, six months ago, when daylight savings was not in effect?


Report •

#13
May 14, 2012 at 16:09:17
It seems that with my latest edit, I've gone from a script that worked only because of On Error Resume Next, to a script with no apparent errors that doesn't do anything. So I am bugfixing the functional portion of this, and once I finish that I will verify whether the updated timezone set solves my original question.

Still curious if this will allow me to run the script against photos taken on the other side of DST, or if I need to find another way to compensate for that.


Report •

#14
May 14, 2012 at 16:43:33
I guess I was assuming the new code was something known to work, and I was only expecting to see the 'lot of errors' in my original you had mentioned.
It was known to work on my systems. My systems are not servers, and I typically don't script for servers.The errors I was getting include:
b.vbs(22, 1) Microsoft VBScript runtime error: Subscript out of range: '[number: 6]'
b.vbs(23, 1) Microsoft VBScript runtime error: Subscript out of range: '[number: 7]'
b.vbs(46, 1) ADODB.Fields: Item cannot be found in the collection corresponding 
to the requested name or ordinal.
At this point, I stopped caring. Especially when the next bit starts modifying file system structure. Besides, I suggested you add a line (well, now 3 lines) and change a line. There isn't that much room for bugs.

If some code works for you, great! Your environment is different from mine. That's why I say my scripts are tested against my available environments, and not debugged.

Does this mean that it will likely be incorrect (an hour off) for pictures taken, for example, six months ago, when daylight savings was not in effect?
I would assume so. If that's an issue, you'll have to manually determine if the photo falls within DST. Win32_SystemTimeZone would probably come in handy. I believe there's still a risk of your photos being an hour off. It's one of those hassles of the DST dates moving.

I'm curious why it's happy with System.ItemPathDisplay yet balks at this
Because you only ask for ItemPathDisplay and DateTaken:
SELECT System.ItemPathDisplay, System.Photo.DateTaken

How To Ask Questions The Smart Way


Report •

#15
May 14, 2012 at 17:06:24
In hindsight, I think some of the errors you may have seen running my script might be due to the way I hard-coded it to work with a specific folder within my DropBox synced storage, which you don't have on your system. That was why I didn't post the script in the beginning; sometimes I think too much info can complicate simple questions.

Besides, I suggested you add a line (well, now 3 lines) and change a line. There isn't that much room for bugs.

That was my original take on it, but like you said, "Debugging typically finds problems with the new stuff; that's kind of the point.

I guess I didn't fully understand what SELECT was doing, thus one of my errors. Thanks for pointing this out.

Anyhow, I re-enabled the popup telling me the timezone offset, and it no longer returns a blank value. This does not provide an automatic method for compensating for daylight savings for all of my photos so far (since a lot of them are from non-DST months), but it at least gets me within an hour of where I want to be. I'll work on fixing other issues before I revisit the daylight discrepancy. I think that effectively addresses the question I asked, if not the question I thought I was asking.

Thanks for your help and patience with a VBScript noob. I will attempt to break it in smarter ways in the future. :)


Report •

#16
July 12, 2012 at 19:50:20
What I was asking for: I'm adding this in case someone else stumbles across this while looking for a similar answer. I wrote the following function as more of what I wanted to accomplish, although I was hoping for a simpler method to maybe ask the OS to return whether a given datetime falls into DST or not. This script is specifically written for Eastern time zone settings, and since the bulk of my pictures have been taken since 2000, this does what I need:

Function EDT(dstValue)
'Analyze a given date and return whether it falls within Eastern Daylight Time
'0 = Eastern Standard Time, "fall back", (UTC - 5)
'1 = Eastern Daylight Time, "spring forward", (UTC - 4)
Dim intHour, intDay, IntMonth, intYear
Dim BeginDSTMonth, BeginDSTDay, EndDSTMonth, EndDSTDay
intHour = Hour(dstValue)
intDay = Day(dstValue)
intMonth = Month(dstValue)
intYear = Year(dstValue)
Select Case intYear
Case 2013 '10MAR - 03NOV
BeginDSTDay = 10
BeginDSTMonth = 3
EndDSTDay = 3
EndDSTMonth = 11
Case 2012 '11MAR - 04NOV
BeginDSTDay = 11
BeginDSTMonth = 3
EndDSTDay = 4
EndDSTMonth = 11
Case 2011 '13MAR - 06NOV
BeginDSTDay = 13
BeginDSTMonth = 3
EndDSTDay = 6
EndDSTMonth = 11
Case 2010 '14MAR - 07NOV
BeginDSTDay = 14
BeginDSTMonth = 3
EndDSTDay = 7
EndDSTMonth = 11
Case 2009 '08MAR - 01NOV
BeginDSTDay = 8
BeginDSTMonth = 3
EndDSTDay = 1
EndDSTMonth = 11
Case 2008 '09MAR - 02NOV
BeginDSTDay = 9
BeginDSTMonth = 3
EndDSTDay = 2
EndDSTMonth = 11
Case 2007 '11MAR - 04NOV
BeginDSTDay = 11
BeginDSTMonth = 3
EndDSTDay = 4
EndDSTMonth = 11
Case 2006 '02APR - 29OCT
BeginDSTDay = 2
BeginDSTMonth = 4
EndDSTDay = 29
EndDSTMonth = 10
Case 2005 '03APR - 30OCT
BeginDSTDay = 3
BeginDSTMonth = 4
EndDSTDay = 30
EndDSTMonth = 10
Case 2004 '04APR - 31OCT
BeginDSTDay = 4
BeginDSTMonth = 4
EndDSTDay = 31
EndDSTMonth = 10
Case 2003 '06APR - 26OCT
BeginDSTDay = 6
BeginDSTMonth = 4
EndDSTDay = 26
EndDSTMonth = 10
Case 2002 '07APR - 27OCT
BeginDSTDay = 7
BeginDSTMonth = 4
EndDSTDay = 27
EndDSTMonth = 10
Case 2001 '01APR - 28OCT
BeginDSTDay = 1
BeginDSTMonth = 4
EndDSTDay = 28
EndDSTMonth = 10
Case 2000 '02APR - 29OCT
BeginDSTDay = 2
BeginDSTMonth = 4
EndDSTDay = 29
EndDSTMonth = 10
Case Else
Exit Function
End Select
EDT = ((intMonth > BeginDSTMonth ) OR _
(intMonth = BeginDSTMonth AND intDay > BeginDSTDay ) OR _
(intMonth = BeginDSTMonth AND intDay = BeginDSTDay AND intHour > 7)) AND _
((intMonth < EndDSTMonth ) OR _
(intMonth = EndDSTMonth AND intDay < EndDSTDay ) OR _
(intMonth = EndDSTMonth And intDay = EndDSTDay AND intHour < 6 ))
End Function

EDT returns a Boolean, so EDT(todaysdate) will return a 1 if todaysdate falls into Eastern Daylight Time or a 0 if not. The only thing I'm unsure of is my math on the intHour part, as the intention is to capture the relative UTC time when EDT starts and ends (at 2am (-5GMT) on the start date we set our clocks forward one hour, and at 2am (-4GMT) on the end date we set our clocks back one hour... It's really a pain to work around, especially now that we've invented the electric lightbulb).


Report •

#17
July 12, 2012 at 20:41:40
There is a setting in the registry that might help:
HKLM\System\CurrentControlSet\Control\TimeZoneInformation

which enumerates "daylight start", "standard start", timezone, and bias (offset from GMT, I think, in minutes in binary).

Then you could just export or query this key and work with it.

ps: ignore...
apologies: didn't read carefully enough your post, regarding multiple years. It's always on Saturday night when the change is applied, (i think) so a formula based on modulo seven should work except for years when they
changed the rules. ANYway, this issue is "solved", so I'll shut up.


Report •

#18
July 13, 2012 at 06:05:00
No worries; this was marked "solved" already, but I figured it might help someone in the future if I post what I later updated to.. I really wish there were some programmatic means of asking the system if a particular date should be modified by DST or not. Surely this sort of thing must be built into my OS since the Date Taken property is stored as UDT and, well, explorer knows to tell me the correct modified date stamp for any date in the past...

The change does always fall on a Sunday morning at 2am, so maybe I can work out an algorithm to replace the case statement. I'll post it here as a followup if I do.


Report •

Ask Question