How to handle timezones in the Query API?

I suppose another option, if it were possible, might be to access the timestamp data on the uncreated record via the Query API. If the API returns that data in the timezone it expects back, then I would have it in the right timezone. I could use that to then run the query I'm wanting to run.

API always uses user's timezone. Hope that explains.

Hey Pavel,



Thanks for that. Maybe you can explain what I'm seeing. Maybe I'm not understanding what's happening here.



The book says (in chapter 6) that merge tokens for date field values will give you the value in the current user's timezone. Is that right? Above you say that the API always uses the user's timezone. So I think that means if I acquire a date value via the API, I'll get the date value in the timezone of the current user. My expectation therefore would be that if I acquire the same date value via a merge token and the API, I'd get the value in the same timezone from both sources. However, that's not what I'm seeing.



Here's what I'm seeing. If I run a formula that acquires the same date value via a merge token, and then immediately again via the API, I get date values that are an hour apart. My first thought was that I must be getting them in different timezones. But maybe something different is causing that hour difference.



Here's the formula:

// Acquire the date value

var start_vi

This will be fixed in the next release.

Thanks very much, Pavel - Nathan

Following up on Pavel's comment, this was fixed in Friday's release (April 27, 2012)

That was fast! Thanks very much for that. I see I'm getting date values in the same time zone now.



It doesn't seem to be my user's time zone, though. I have my time zone set to GMT-7, and I'm getting date values from the above formula in GMT-5. I see the account settings have the time zone set to GMT-5.



Does the server give me the account time zone rather than the viewing user's time zone?

Actually, I just noticed that the time value I get does change when I change the time zone in my user settings.



I'm not really sure what's going on here, but something doesn't seem right.



My user account has MST: (GMT-07:00) MST

The account settings has EST: (GMT -05:00) EST



Merge token date strings give me MDT: Thu Apr 05 2012 04:00:00 (MDT)

Date objects from them are CDT: Thu Apr 05 2012 04:00:00 GMT-0500 (CDT)

Date objects from the API are also CDT: Thu Apr 05 04:00:00 CDT 2012



If I change my time zone (in my user account), the time value I get does change. BUT, the time zone on the date/time string remains at CDT, no matter what I do. I don't know if that time zone label is just a string somewhere, or if it's actually the time zone of the JS Date object.



I think the MDT in the merge token date string is actually correct. I believe Mountain Time is currently in daylight savings time, so it becomes Mountain Daylight Time. I have no idea where the CDT is coming from.



If the JS Date objec

Here's an updated formula that also outputs the merge token string Before it's converted to a JS date object.



Formula:

// Acquire the merge token string



var start_merge_token_string = '{!worked_time_start}';



rbv_api.println ('Acquired merge token string: start_merge_token_string=' + start_merge_token_string);



// Acquire the date value from both sources



var start_via_merge_token = new Date (start_merge_token_string);



var start_via_api = rbv_api.selectValue ('SELECT worked_time_start FROM worked_time WHERE id=?', {!id});



rbv_api.println ('Acquired date value: start_via_merge_token=' + start_via_merge_token + ', start_via_api=' + start_via_api);



// Convert to milliseconds



var start_via_merge_token_ms = start_via_merge_token.getTime ();



var start_via_api_ms = start_via_api.getTime ();



rbv_api.println ('Converted to milliseconds: start_via_merge_token_ms=' + start_via_merge_token_ms + ', start_via_api_ms=' + start_via_api_ms);



// Compare for equality



rbv_api.println ('Equality comparison

Here's an example of what happens when I change my user time zone. I'm acquiring the same date value from the same record, with nothing but my user time zone changed:



With my time zone as MST (GMT -07:00):

Via merge token: Thu Apr 05 2012 04:00:00 GMT-0500 (CDT)

Via API: Thu Apr 05 04:00:00 CDT 2012



With my time zone as CST (GMT -06:00):

Via merge token: Thu Apr 05 2012 05:00:00 GMT-0500 (CDT)

Via API: Thu Apr 05 05:00:00 CDT 2012



With my time zone as EST (GMT -05:00):

Via merge token: Thu Apr 05 2012 06:00:00 GMT-0500 (CDT)

Via API: Thu Apr 05 06:00:00 CDT 2012

So it seems to be working fine now.

Thanks for looking at this, Pavel. It seems to me that something is a bit off.



Here's a test I ran. I queried the same date value from the same field on the same record multiple times. Between queries, I changed one thing: my user timezone setting. When I check the UTC time for the Date objects I get, it's different every time.



My expectation here would be that the UTC time for the same date value is the same every time I query it, regardless of the timezone of the current user. I would expect the timezone of the object to change, but not the underlying, absolute UTC time value.



For interest, when my user timezone setting is GMT, the standard UI displays the date value I'm querying as:

2012-04-05 11:00

With timezone GMT:



start.toUTCString=Thu, 05 Apr 2012 16:00:00 GMT







With timezone IST (GMT+05:30, India time):



start.toUTCString=Thu, 05 Apr 2012 20:30:00 GMT







With timezone JST (GMT+09:00, Japan time):



start.toUTCString=Fri, 06 Apr 2012 00:00:00 GMT







With timezone 

Here's the same set of queries again, this time displaying the results of the getTime() method. This method returns the milliseconds-since-epoch for a time value. I think the milliseconds value is intended to represent an absolute point in time, independent of timezone.



Again, my expectation here would be that the milliseconds value is the same every time I query the same date value, regardless of the timezone setting of the current user.

With timezone GMT:



start.getTime=1333641600000







With timezone IST (GMT+05:30, India time):



start.getTime=1333657800000







With timezone JST (GMT+09:00, Japan time):



start.getTime=1333670400000







With timezone MST (GMT-07:00, Mountain Time):



start.getTime=1333616400000







With timezone CST (GMT-06:00, Central Time):



start.getTime=1333620000000







With timezone EST (GMT-05:00, Eastern Time):



start.getTime=1333623600000

var start = new Date ('{!worked_time_start}');



rb

This seems to be affecting date comparisons. I can't seem to run a comparison successfully.



If I pull a date value from an existing record, then immediately query the database for any records matching that date value, I get zero matches. My expectation here would be that if I query for the value of an existing record, I would get a match on at least that record.



Here's a formula that pulls a date value via the API, then immediately queries for it:

var start = rbv_api.selectValue ('SELECT worked_time_start FROM worked_time WHERE id=?', {!id});



var num_matches = rbv_api.selectNumber ('SELECT COUNT(1) FROM worked_time WHERE worked_time_start=?', start);



rbv_api.println ('num_matches=' + num_matches);

var start = new Date ('{!worked_time_start}');



var num_matches = rbv_api.selectNumber ('SELECT COUNT(1) FROM worked_time WHERE worked_time

This type of queries will not work since in DB date/time is always stored in server's time zone. I can try to fix this.

Thanks, Pavel. It would be helpful to me if I could run queries like that.