date formats in project_nvps

Hi

I'm using the WorkSite web services to create a folder (CreateFolders method), which seems to work perfectly fine except for Custom 24 (date field).

There is no error message generated, but when you look at the folders properties after is is created, you see only "01/01/1970" (in the Profile tab of the folder properties).

I have noticed that in the project_nvps table, that dates are not stored like a date - they are long numeric strings. The one I created is stored like a normal date, however, so I'm guessing that is the cause of the problem.

Could you tell me how to generate these strings that represent dates on folder profiles?

Thanks
Gary

Find more posts tagged with

Comments

jny

The custom date fields use Unix UTC time format. Here's a code example to do the conversion:

[VBNet]

Imports System.DateTime

Dim dInValue As Date = "10/22/07"
Dim strUnixUTCTime As String = ((dInValue.ToFileTimeUtc() - 116444736000000000) / 10000000).ToString() + 0

myDocumentFolder.AdditionalProperties.Add(iMan___45, strUnixUTCTime)

Migrateduser

How can a date in this format be converted back to a "normal" date to apply to a document profile using SetAttributeById?

jny

This is the VB function I wrote to return the date, but I haven't the chance to test/convert to .NET yet:

Public Declare Function FileTimeToLocalFileTime Lib "kernel32" (lpFileTime As Any, lpLocalFileTime As Any) As Long

Private Function Win32ToVbTime(ft As Currency) As Date

Const rDayZeroBias As Single = 109205# 'CDbl(#1/1/1601#)
Const rMillisecondPerDay As Double = (10000000# * 60# * 60# * 24#) / 10000#

Dim ftl As Currency

' Call API to convert from UTC time to local time
If FileTimeToLocalFileTime(ft, ftl) Then
Win32ToVbTime = CDate((ftl / rMillisecondPerDay) - rDayZeroBias)
'Dim lDays As Long
'lDays = DateDiff("YYYY", Win32ToVbTime, "11/7/2005")
Dim ret As Variant
Dim dbNumOfYrs As Double
dbNumOfYrs = 369
ret = DateAdd("YYYY", dbNumOfYrs, Win32ToVbTime)

'Win32ToVbTime = Win32ToVbTime + 134685
End If
End Function

Private Sub Form_Load()

...

Dim s21 As String
s21 = fldr.AdditionalProperties.ItemByName("iMan___45").Value

Dim d21 As Date
d21 = Win32ToVbTime(Trim(CCur(s21)))

End Sub

Migrateduser

THANKS! That works great! I added a GMT conversion to correct for local time, but does this really matter in this context? Despite the fact that this field (at least on profiles) only stores a Date, the data supports a time. Is there an inherent risk in this, if we have a folder that is created in GMT+12 at 10:00 AM, and the conversion is made in GMT - 5, it will appear in the prior day. I suppose this issue appears in native WorkSite the way it is now...

What is the thinking behind using the UTC time, which is expressed in different time zones, on a "date" field?

jny

If your folder has date fields profiled, and you would like to push those dates to the document programmatically, the better approach would be to use the IMANEXT2Lib.IManRefileCmd instead of using the conversion function (provided in my previous response) to get the actual date to add to the document profile via the SetAttributeByID Method.

The IManage.dll sets the time zone offset to this regkey setting:

HKLM\SOFTWARE\Interwoven\WorkSite\imDmsSvc
"UTC In Use"="Y"

When you log onto a dms object, you will see this setting as one of the items inside the ConfigurationData (Session) Collection, which is read-only. Based on this setting, the back-end server will convert everything from local time to UTC time. All of this should be transparent to the API calls, such as the file create and edit dates. The only exception is the custom date fields. Those fields respect the local time zone (to the Client Machine).

For the nvp's, specifically the 'iMan___45" through "iMan___48", it's always stored as UTC, represented by a string. But if you ever re-file using the IManExt2.RefileCmd or from within a WorkSite Client, the document custom date fields should have the local time to the client machine.

In other words, if you use the automated command object, IManRefileCmd, instead of the low-level (IManage.dll) API's, the document profile will have the local time (to the client machine) in its custom date fields.

Migrateduser

I spoke too soon. This does not appear to be working correctly. Here are cleaned-up versions of your code:

To get a date from the UTC date value:

Public Declare Function FileTimeToLocalFileTime Lib "kernel32" (lpFileTime As Any, lpLocalFileTime As Any) As Long

Public Function UTC2DateTime(ft As Currency) As Date

Const rDayZeroBias As Single = 109205# 'CDbl(#1/1/1601#)
Const rMillisecondPerDay As Double = (10000000# * 60# * 60# * 24#) / 10000#

Dim ftl As Currency

' Call API to convert from UTC time to local time
If FileTimeToLocalFileTime(ft, ftl) Then
UTC2DateTime = CDate((ftl / rMillisecondPerDay) - rDayZeroBias)
Dim ret As Variant
Dim dbNumOfYrs As Double
dbNumOfYrs = 369
ret = DateAdd("YYYY", dbNumOfYrs, UTC2DateTime)
UTC2DateTime = CDate(ret)
End If
End Function

Note that according to Microsoft (http://msdn2.microsoft.com/en-us/library/ms724277.aspx), the FileTimeToLocalFileTime function returns "UTC-based file time ... converted into a local file time", which is one area where this could introduce inaccuracies of up to 1 day. If the Folder's custom date field value was set on a machine in GMT +10 before 10:00 AM, and the value was converted on a machine in GMT -5, the date value returned will be the prior day.

The other side of this is how to convert a date as it is stored in a custom date field on a WorkSpace to the UTC value to be set on a Folder, then set it back from the folder to the document. The custom date value on the WorkSpace is an actual Date, e.g., "11/21/2007". I am trying various things to convert this to the UTC value, such as:

Public Declare Function SystemTimeToFileTime Lib "kernel32" (lpSystemTime As Any, lpFileTime As Any) As Long

Public Function Date2UTC(ft As Date) As String
Dim iDate As SYSTEMTIMEREC
Dim utctime As Currency
iDate.wYear = Format(ft, "yyyy")
iDate.wMonth = Format(ft, "m")
iDate.wDay = Format(ft, "d")
iDate.wHour = Format(ft, "h")
iDate.wMinute = Format(ft, "n")
iDate.wSecond = Format(ft, "s")
If SystemTimeToFileTime(iDate, utctime) Then Date2UTC = utctime
End Function

But this returns a value that is exactly 369 years and -5 hours (I am in GMT-5) off.

Questions:

1. Is there a better/different way to convert the Date to the "UTC" value that is correct for a folder's custom date NVP?
2. Shouldn't the function to return the date from the UTC value do comething different with the UTC time value?

What is happening is the following:

I set the custom date value on the WorkSpace. This is set to a Date value in SQL.
I set the same Date value on a Folder, it is added to the NVP in a UTC format that includes the GMT time offset. If the Workspace is refiled in different time zones, does this value get changed?
I convert the UTC value from the folder to a date value to apply to a document, the GMT offset is applied AGAIN in the function, creating a possible 2 day error in the data!

Thanks in advance for any advice.

jny

My point earlier was that you should use the IManRefileCmd to re-file the documents instead of calling the SetAttributeByID method to push each profile fied value, and managing your own conversion for the custom date fields, of the folder down to the document.

Put each document you would like to inherit the folder's profile and security in a safe array then call the refile command.

Context.Add "SelectedIManObjects", arrDox
Context.Add "SelectedContentsContainer", oFldr ' Destination folder
Context.Add "ConfirmMessages", True
Context.Add "IManExt2.RefileNoUI", True

The document profile(s) should now have the local time (to the client machine) in its custom date fields.

Migrateduser

That would be great if this is what we wanted to do. We do not want to refile the documents through the WorkSite APIs. This is for our Refiling Server products:

http://www.docauto.com/wsrefsvrent.htm

and

http://www.docauto.com/wsrefsvr.htm

Where we are talking a little finer control of the refiling process.

The bottom line is, how do I take a custom Date value and be able to set it RELIABLY on

WorkSpace -> Folder -> Document

In such a way that if a user manually edits a profile of a WorkSpace or Folder (without necessarily changing a value, but clicks OK so that the native Refile processing is triggered) so that the date will be correct on all three levels regardless of the time zone the user is in?

- I can set a date on a WorkSpace easily, by simply setting th date attribute to a real date.
- I can convert the UTC date from a Folder to a date (although this introduces potential GMT offset inaccuracies) and set this value on documents.
- How can I take a Date and convert it to the UTC format that Interwoven uses for Folder NVP values?

jny

I know you have your own refiling products...I know that through the history of the DevSupport cases. Thanks for the links though... Smiley Happy

At first it sounded like you were trying to set the document date fields by the folder date fields. This isn't actually the case, then??

Now, it sounds more like you're trying to programmatically set the UTC format on the folder's date field. Is that it? If so, to convert date to UTC, this is the function I use:

Private Function ConvertDatetoUnixUTC(ByVal vDate As Date) As String

On Error GoTo ErrHandler

ConvertDatetoUnixUTC = ""

Dim Date2 As Date
Dim Date1 As Date
Dim strValue As String

Date1 = CDate("01/01/1970 00:00:00")
Date2 = vDate

strValue = DateDiff("s", Date1, Date2)
strValue = strValue & "000"

ConvertDatetoUnixUTC = strValue

Exit Function
ErrHandler:
'DebugPrint Err.Description
Err.Clear

End Function

Which should be the equivalent of this (given in the earlier correspondence):

Imports System.DateTime

Dim dInValue As Date = "10/22/07"
Dim strUnixUTCTime As String = ((dInValue.ToFileTimeUtc() - 116444736000000000) / 10000000).ToString() + 0

If this is not at all it then you would need to explain to me again. Sorry, I'm confused at this point on exactly what you're looking to accomplish.

Migrateduser

My initial need was to extract dates from folder nvps, but now I need to do more than that. I need to be able to consistently apply custom Date values to WorkSpaces, Folders, and Documents.

The problem is that these two functions do not return the same results, regardless of GMT issues. Here are your two functions:
Public Declare Function FileTimeToLocalFileTime Lib "kernel32" (lpFileTime As Any, lpLocalFileTime As Any) As Long

Public Function UTC2DateTime(ft As Currency) As Date

Const rDayZeroBias As Single = 109205# 'CDbl(#1/1/1601#)
Const rMillisecondPerDay As Double = (10000000# * 60# * 60# * 24#) / 10000#

Dim ftl As Currency

' Call API to convert from UTC time to local time
If FileTimeToLocalFileTime(ft, ftl) Then
UTC2DateTime = CDate((ftl / rMillisecondPerDay) - rDayZeroBias)
Dim ret As Variant
Dim dbNumOfYrs As Double
dbNumOfYrs = 369
ret = DateAdd("YYYY", dbNumOfYrs, UTC2DateTime)
UTC2DateTime = CDate(ret)
End If
End Function

Public Function Date2UTC(ByVal vDate As Date) As String

On Error GoTo ErrHandler

Date2UTC = ""

Dim Date2 As Date
Dim Date1 As Date
Dim strValue As String

Date1 = CDate("01/01/1970 00:00:00")
Date2 = vDate

strValue = DateDiff("s", Date1, Date2)
strValue = strValue & "000"

Date2UTC = strValue

Exit Function
ErrHandler:
Err.Clear

End Function

Here are the results of processing these:

date2utc("11/21/2007 1:46:58 PM") = 1195652818000
utc2datetime(1195652818000) = "11/22/2007 3:13:02 PM"

Which is a difference of 91564 seconds, more than a day and an hour off.

Another example:

date2utc("1/1/1970") = 0000
utc2datetime(0000) = "1/1/1970 5:00:00 AM"

Which is 5 hours off (the GMT offset here).

jny

Sine the SDK does not support this, I merely try using a homegrown Windows function, namely the FileTimeToLocalFileTime(). You may try other Windows functions, for instance, the FileTimeToSystemTime() to see whether or not it yields an universal result.

As far as the convertToUTC, you may need to tweek with it to get it to work with the correct calculation. I haven't really touched that code for a long time, and it's just an approach to converting UTC; it is not an absolute solution. I'm sure there are other functions in .NET or Windows that can more accurately do the UTC conversion. I usually start with Google based on the language I use at the time.

If you need more help on this, request your current case to stay open and I'll have to look into this on Monday. Because this isn't a supported feature in the SDK, it is also necessary to have the sufficient assigned bandwidth to find a way to supplement this lack of feature.

In all, this should truely be an enhancement request for R&D to support.

Migrateduser

The simple solution is to NOT use the Windows UTC conversion routines, since times should be irrelevant in the date custom fields anyway:

Public Function UTC2DateTime(ByVal ft As Currency) As Date
UTC2DateTime = DateAdd("s", ft / 1000, "1/1/1970 00:00:00")
End Function

Public Function Date2UTC(ByVal vDate As Date) As String

On Error GoTo ErrHandler

Date2UTC = ""

Dim Date2 As Date
Dim Date1 As Date
Dim strValue As String

Date1 = CDate("01/01/1970 00:00:00")
Date2 = vDate

strValue = DateDiff("s", Date1, Date2)
strValue = strValue & "000"

Date2UTC = strValue

Exit Function
ErrHandler:
Err.Clear

End Function

The Interwoven functions really have nothing to do with TRUE UTC values: they are merely relative computations of milliseconds from 1/1/1970 00:00:00.

jny

The simple solution is to NOT use the Windows UTC conversion routines, since times should be irrelevant in the date custom fields anyway:

Public Function UTC2DateTime(ByVal ft As Currency) As Date
UTC2DateTime = DateAdd("s", ft / 1000, "1/1/1970 00:00:00")
End Function

Public Function Date2UTC(ByVal vDate As Date) As String

On Error GoTo ErrHandler

Date2UTC = ""

Dim Date2 As Date
Dim Date1 As Date
Dim strValue As String

Date1 = CDate("01/01/1970 00:00:00")
Date2 = vDate

strValue = DateDiff("s", Date1, Date2)
strValue = strValue & "000"

Date2UTC = strValue

Exit Function
ErrHandler:
Err.Clear

End Function

The Interwoven functions really have nothing to do with TRUE UTC values: they are merely relative computations of milliseconds from 1/1/1970 00:00:00.

I've finally gotten a chance to review our source code to see how we re-file date fields, and this is how we do it:

DOUBLE ConvertUnixUTCToVariantTime(LONG ltime)
{
FILETIME ft;
LONGLONG ll = Int32x32To64(ltime, 10000000) + 116444736000000000;

ft.dwLowDateTime = (DWORD)ll;
ft.dwHighDateTime = (DWORD)(ll >> 32);

SYSTEMTIME st;
FileTimeToSystemTime(&ft, &st);

DOUBLE vDate;
SystemTimeToVariantTime(&st, &vDate);

return vDate;
}

LONG ConvertVariantTimeToUnixUTC(_variant_t varDateTime)
{
SYSTEMTIME St;

VariantTimeToSystemTime( varDateTime, &St );

FILETIME fileTime;

SystemTimeToFileTime(&St,&fileTime);
/*#31369 After changing the time zone to GMT +1:00 Amsterdam,Berlin, bern, Rome, stockholm,Vienna on the client workstation, when a user selects a date in the custom21 fields in the folder profile properties,
the date is one day off after the user goes back in the the folder profile properties...
FILETIME UTCFileTime;
LocalFileTimeToFileTime(&fileTime,&UTCFileTime);

LONGLONG wUTC = (((LONGLONG)UTCFileTime.dwHighDateTime) << 32) + UTCFileTime.dwLowDateTime;
*/
LONGLONG wUTC = (((LONGLONG)fileTime.dwHighDateTime) << 32) + fileTime.dwLowDateTime;
LONG lTime = (LONG)(((LONGLONG)wUTC - 116444736000000000) / 10000000);

return lTime;
}

Notice we do account for the file time as well as the one-day difference from Europe.