Timezone Conversion

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Timezone Conversion

Justin Dennis-3
'morning -

I have been hitting my head against a brick wall with timezone conversion.
Right now, we do a lot of this at the SQL level in MySQL:

CONVERT_TZ("2015-12-31 23:00:00", "US/Arizona", "US/Eastern")

This works great, because it takes into account when US/Eastern means EDT
or EST while Arizona does not do daylight savings time.

I was hoping to find a list of timezones supported by the ICU library, but
no luck. In fact, their documentation seems to suggest that they don't even
address "generic" timezones like "ET", instead requiring "EST" or "EDT". I
was hoping to delegate the daylight savings time determination to someone
(e.g. MySQL, ICU, Lasso) who can do it better than me.

Our database stores all dates and times in our HQ's local timezone, which
is "ET". So every time a user enters a local time, we have to convert
before storing.

How do others deal with this issue? Thanks for any insights, and I wish
everyone a happy New Year!

- Justin

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Timezone Conversion

stevepiercy
Always store datetimes as UTC. When displaying datetimes to the user, format in their time zone or leave it as UTC.

My article discusses and provides references to time zone conversion, including an excellent video presentation from PyCon 2012, "What you need to know about datetimes", which summarizes the complexities of date and time.

http://www.stevepiercy.com/articles/tz_convert-convert-datetime-types-between-time-zones-in-lasso-8/

http://pyvideo.org/video/946/what-you-need-to-know-about-datetimes

--steve

On Dec 31, 2015 10:29 AM, Steve Piercy - Website Builder <[hidden email]> wrote:

>
> Always store datetimes as UTC. When displaying datetimes to the user, format in their time zone or leave it as UTC.
>
> My article discusses and provides references to time zone conversion, including an excellent video presentation from PyCon 2012, "What you need to know about datetimes", which summarizes the complexities of date and time.
>
> http://www.stevepiercy.com/articles/tz_convert-convert-datetime-types-between-time-zones-in-lasso-8/
>
> http://pyvideo.org/video/946/what-you-need-to-know-about-datetimes
>
> --steveOn Dec 31, 2015 8:15 AM, Justin Dennis <[hidden email]> wrote:
> >
> > 'morning -
> >
> > I have been hitting my head against a brick wall with timezone conversion.
> > Right now, we do a lot of this at the SQL level in MySQL:
> >
> > CONVERT_TZ("2015-12-31 23:00:00", "US/Arizona", "US/Eastern")
> >
> > This works great, because it takes into account when US/Eastern means EDT
> > or EST while Arizona does not do daylight savings time.
> >
> > I was hoping to find a list of timezones supported by the ICU library, but
> > no luck. In fact, their documentation seems to suggest that they don't even
> > address "generic" timezones like "ET", instead requiring "EST" or "EDT". I
> > was hoping to delegate the daylight savings time determination to someone
> > (e.g. MySQL, ICU, Lasso) who can do it better than me.
> >
> > Our database stores all dates and times in our HQ's local timezone, which
> > is "ET". So every time a user enters a local time, we have to convert
> > before storing.
> >
> > How do others deal with this issue? Thanks for any insights, and I wish
> > everyone a happy New Year!
> >
> > - Justin
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >   the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com 
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Timezone Conversion

Brad Lindsay
In reply to this post by Justin Dennis-3
I think the best-practice way to do it is to store the data as UTC/GMT and then do the conversion when you want to display it.

For ICU formatting stuff, you may want to look at the v or V options. (In terms of a list, for the latter, take a look here: http://www.unicode.org/repos/cldr/tags/latest/common/bcp47/timezone.xml)


To get the current date/time in GMT/UTC: [date(-dateGMT)]
Here’s some examples of working with the aliases in the link above in Lasso:


// This gets a date object with the timezone set to AZ
// Normally, all dates would be GMT, but this is to show
// an example of dealing with other data
local(az_date) = date("2015-05-22 10:00:00 US/Arizona", -format='yyyy-MM-dd HH:mm:ss VV')

#az_date->timezone
// => US/Arizona

#az_date
// => 2015-05-22 10:00:00 US/Arizona

// Now let’s get the ET version:
local(et_date) = #az_date->asCopy
#et_date->timezone = "US/Eastern"
#et_date
// => 2015-05-22 13:00:00 US/Eastern

HTH,
Brad

On December 31, 2015 at 11:15:42 AM, Justin Dennis ([hidden email]) wrote:

> 'morning -
>  
> I have been hitting my head against a brick wall with timezone conversion.
> Right now, we do a lot of this at the SQL level in MySQL:
>  
> CONVERT_TZ("2015-12-31 23:00:00", "US/Arizona", "US/Eastern")
>  
> This works great, because it takes into account when US/Eastern means EDT
> or EST while Arizona does not do daylight savings time.
>  
> I was hoping to find a list of timezones supported by the ICU library, but
> no luck. In fact, their documentation seems to suggest that they don't even
> address "generic" timezones like "ET", instead requiring "EST" or "EDT". I
> was hoping to delegate the daylight savings time determination to someone
> (e.g. MySQL, ICU, Lasso) who can do it better than me.
>  
> Our database stores all dates and times in our HQ's local timezone, which
> is "ET". So every time a user enters a local time, we have to convert
> before storing.
>  
> How do others deal with this issue? Thanks for any insights, and I wish
> everyone a happy New Year!
>  
> - Justin
>  
> #############################################################
>  
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to:  
> Send administrative queries to  
>  


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Timezone Conversion

Rick Draper-2
In reply to this post by Justin Dennis-3
Hi Justin,

Steve and Brad advice about UTC is well placed.  But I would add a few
considerations into the mix, as a lot depends on what the date/time means
when it is retrieved.  Some considerations:

* Was the event (time recorded) actually related to an activity at a
location?  (in this case a user is likely expecting to see date/time data
based on the time zone of the location)
* Is the person reviewing the data needing to communicate the date/time to
another person in another time zone
* Do you need to display granular data that spans daylight saving
transitions?  E.g. Events that will be like 01:59 EDT followed by 02:00 EST
(displaying the time zone in which the event occurred becomes important for
absolute clarity)
* Do you need to know what time the user initiating an even thought it was
(what time did they submit the form according to their computer? Does it
matter if their computer clock is WAY OUT?)
* Will you need to give evidence about what time the event was recorded on
your server?

We had a situation where users would change the time on their computers
trying to get around time restrictions for submitting reports.  Anyway, we
adopted a standard practice of recording dates and times in three formats
(Server Time, UTC, and User's Local Time) - It is extra data, but gives some
flexibility around displaying date/time data in context.

I hope this is helpful and not just adding confusion.

Very best regards,

Rick
------------------
Rick Draper





#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Timezone Conversion

Bil Corry-3
Related, it is possible to get the timezone offset for the user via
JavaScript, if that helps with data entry:

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset

And this looks feature-rich for picking a timezone:

http://timezonepicker.com/


- Bil



On Thu, Dec 31, 2015 at 10:00 PM, Rick Draper <[hidden email]>
wrote:

> Hi Justin,
>
> Steve and Brad advice about UTC is well placed.  But I would add a few
> considerations into the mix, as a lot depends on what the date/time means
> when it is retrieved.  Some considerations:
>
> * Was the event (time recorded) actually related to an activity at a
> location?  (in this case a user is likely expecting to see date/time data
> based on the time zone of the location)
> * Is the person reviewing the data needing to communicate the date/time to
> another person in another time zone
> * Do you need to display granular data that spans daylight saving
> transitions?  E.g. Events that will be like 01:59 EDT followed by 02:00 EST
> (displaying the time zone in which the event occurred becomes important for
> absolute clarity)
> * Do you need to know what time the user initiating an even thought it was
> (what time did they submit the form according to their computer? Does it
> matter if their computer clock is WAY OUT?)
> * Will you need to give evidence about what time the event was recorded on
> your server?
>
> We had a situation where users would change the time on their computers
> trying to get around time restrictions for submitting reports.  Anyway, we
> adopted a standard practice of recording dates and times in three formats
> (Server Time, UTC, and User's Local Time) - It is extra data, but gives
> some
> flexibility around displaying date/time data in context.
>
> I hope this is helpful and not just adding confusion.
>
> Very best regards,
>
> Rick
> ------------------
> Rick Draper
>
>
>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Timezone Conversion

Ke Carlton-3
Yep; normally wrapped via momentjs — although that still doesn't help if
they have screwy machine settings....

Ke

On Sat, Jan 2, 2016 at 1:35 AM Bil Corry <[hidden email]> wrote:

> Related, it is possible to get the timezone offset for the user via
> JavaScript, if that helps with data entry:
>
>
> https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset
>
> And this looks feature-rich for picking a timezone:
>
> http://timezonepicker.com/
>
>
> - Bil
>
>
>
> On Thu, Dec 31, 2015 at 10:00 PM, Rick Draper <[hidden email]
> >
> wrote:
>
> > Hi Justin,
> >
> > Steve and Brad advice about UTC is well placed.  But I would add a few
> > considerations into the mix, as a lot depends on what the date/time means
> > when it is retrieved.  Some considerations:
> >
> > * Was the event (time recorded) actually related to an activity at a
> > location?  (in this case a user is likely expecting to see date/time data
> > based on the time zone of the location)
> > * Is the person reviewing the data needing to communicate the date/time
> to
> > another person in another time zone
> > * Do you need to display granular data that spans daylight saving
> > transitions?  E.g. Events that will be like 01:59 EDT followed by 02:00
> EST
> > (displaying the time zone in which the event occurred becomes important
> for
> > absolute clarity)
> > * Do you need to know what time the user initiating an even thought it
> was
> > (what time did they submit the form according to their computer? Does it
> > matter if their computer clock is WAY OUT?)
> > * Will you need to give evidence about what time the event was recorded
> on
> > your server?
> >
> > We had a situation where users would change the time on their computers
> > trying to get around time restrictions for submitting reports.  Anyway,
> we
> > adopted a standard practice of recording dates and times in three formats
> > (Server Time, UTC, and User's Local Time) - It is extra data, but gives
> > some
> > flexibility around displaying date/time data in context.
> >
> > I hope this is helpful and not just adding confusion.
> >
> > Very best regards,
> >
> > Rick
> > ------------------
> > Rick Draper
> >
> >
> >
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >   the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>
> >
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>