Re: [sqlite] Handling Timezones

[Jim Callahan]

I assume you know about the timezone (tz) database maintained by the
Internet Assigned Numbers Authority (IANA) -- it is sometimes referred as
the Eggert/Olson database -- after its code and data maintainers.

http://www.iana.org/time-zones

"The tz database is published as a set of text files
 which list the rules and zone
transitions in a human-readable format. For use, these text files are
compiled  into a set of
platform-independent binary files
—one
per time zone. The reference source code includes such a compiler called
*zic* (zone information compiler), as well as code to read those files and
use them in standard APIs
 such as
localtime() and mktime()....The Olson timezone IDs are also used by the
Unicode Common Locale Data Repository
 (CLDR)
and International
Components for Unicode
 (ICU).
For example, the CLDR Windows–Tzid table maps Microsoft Windows time zone
IDs to the standard Olson names."
http://en.wikipedia.org/wiki/Tz_database

I assume the tz database could be imported into SQLite assuming that's not
what they are using already.

As long as time and date strings have leading zeros ("01") and not just
("1") it is easy to use SQLite string functions to pull them apart and use
in calculations, but using this method you have to keep track of the
modulus (2 AM - 5 hours is?) yourself.

I learned about the "Olson database" from Wes McKinney's book "Python for
Data Analysis." CAUTION: check the book's errata website
http://oreil.ly/python_for_data_analysis
before following any instructions in the book -- the field is changing very
rapidly and in just over a year -- several key instructions (including
which Python distribution to use) are already out of date. Wes has two
sections on time zones:
pages 18-26 where he downloads a usgov_bitly dataset and analyzes the time
zone
and pages 303-306 where he analyzes date stamps and mentions the ptz AND
pandas Python libraries and the "Olson database"

Jim Callahan
Orlando, FL







On Tue, Jul 29, 2014 at 9:41 AM, Will Fong  wrote:

> Hi,
>
> How are timezones best handled? Since dates are stored in GMT, when I
> go to display them, I need to add/subtract the timezone. That's not
> too hard when I can just store the timezone as "-5" for EST. When I'm
> providing a date to query on, I would have to apply the reverse of the
> timezone, "+5", to normalize it to GMT.
>
> That kinda sucks :(
>
> I come from PostgreSQL, so I normally set at the connection level the
> timezone and PG handles all the conversions. Does SQLite have a
> similar feature?
>
> Is there a standard way to handle this?
>
> Thanks,
> -will
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Tim Streater]

On 02 Aug 2014 at 00:27, Stephen Chrzanowski  wrote: 

> I understand that with routing and such, you can end up outside where you
> really are (With my IP, I'm shown just outside of Toronto when I'm actually
> two hours out), but the chances of showing up in Taiwan when you're in
> Tennessee is doubtful.

More to do with what the owner of the IP address block decides to do, than with 
routing. They could easily have a world-wide or continent-wide network, in 
which case your IP address tells you nothing at all.

On OS X, I appear to be able to do it thus (in PHP):

  $zonepath = @readlink ('/etc/localtime');   // Path to the timezone 
file
  if  ($zonepath===false)  exit (1);  // User needs to set 
timezone

  $leadin   = '/usr/share/zoneinfo/'; // Strip off the leadin 
and
  $timezone = substr ($zonepath, strlen($leadin));// the timezone remains

Whether this generalises to other unix flavours I don't know. I was unable to 
find a reliable way to do similar under Windows, though it may exist.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Stephen Chrzanowski]

I suppose either way you're going to run into accuracy problems, but that
is the nature of the beast.  IP wasn't ever developed to be a geographical
thing, but we're trying to get geographical info from 'guessing'.  I guess
it boils down to one of three things;

A> Where are Wills users going to be when accessing the site?  Internal or
external?  From a hotel room or from another office?
B> Can you trust that the user will change their clocks on their machines?
C> Does Wills company go with that kind of network routing?  (I don't go to
hotels often, so I can't state whether each building has their own external
IP or if they route as you suggest)

If "A", and if they're using internal IPs, you could still get a good idea
of where the users are based on their internal IP, pending you're not
running the same network range in multiple locations.  (Network guys love a
challenge sometimes. ;))

I suppose another option would be that when a user logs into whatever
service Will is offering, then a field asking for the current local time
would get the most real accurate time.

On Fri, Aug 1, 2014 at 7:32 PM, Donald Shepherd 
wrote:

> Actually what Rob and I were pointing out was that the chances of showing
> up in Taiwan when you're in Tennessee is actually quite high in a corporate
> environment - he gets moved from the UK to Germany, I get moved from
> Australia to Phoenix, AZ, my wife gets moved from Australia to Switzerland
> and that's just a random sample.  It's not uncommon at all for large
> companies to route traffic through a single gateway, and as a result using
> geolocation to detect timezones is very prone to problems if people want to
> access a site from inside a large company, whereas using client-based logic
> avoids this.
>
>
> On 2 August 2014 09:27, Stephen Chrzanowski  wrote:
>
> > I understand that with routing and such, you can end up outside where you
> > really are (With my IP, I'm shown just outside of Toronto when I'm
> actually
> > two hours out), but the chances of showing up in Taiwan when you're in
> > Tennessee is doubtful.  The point of the matter is that you'll get real
> > time data in regards to where the user might be located and from there,
> > you'll get a general idea on when a good time to call is.
> >
> > There are also bounce VPNs which would make it look like I'm in Texas
> when
> > I'm in Toronto.  Depending on how I route my traffic here, I can be
> > anywhere in the world.
> >
> >
> > On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:
> >
> > > Hi everyone,
> > >
> > > Wow, such great responses! So my background is not with this type of
> > > development, so I never really thought about these types of problems
> > > before. Thank you all for the help!
> > >
> > > -will
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Donald Shepherd]

Actually what Rob and I were pointing out was that the chances of showing
up in Taiwan when you're in Tennessee is actually quite high in a corporate
environment - he gets moved from the UK to Germany, I get moved from
Australia to Phoenix, AZ, my wife gets moved from Australia to Switzerland
and that's just a random sample.  It's not uncommon at all for large
companies to route traffic through a single gateway, and as a result using
geolocation to detect timezones is very prone to problems if people want to
access a site from inside a large company, whereas using client-based logic
avoids this.


On 2 August 2014 09:27, Stephen Chrzanowski  wrote:

> I understand that with routing and such, you can end up outside where you
> really are (With my IP, I'm shown just outside of Toronto when I'm actually
> two hours out), but the chances of showing up in Taiwan when you're in
> Tennessee is doubtful.  The point of the matter is that you'll get real
> time data in regards to where the user might be located and from there,
> you'll get a general idea on when a good time to call is.
>
> There are also bounce VPNs which would make it look like I'm in Texas when
> I'm in Toronto.  Depending on how I route my traffic here, I can be
> anywhere in the world.
>
>
> On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:
>
> > Hi everyone,
> >
> > Wow, such great responses! So my background is not with this type of
> > development, so I never really thought about these types of problems
> > before. Thank you all for the help!
> >
> > -will
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Stephen Chrzanowski]

I understand that with routing and such, you can end up outside where you
really are (With my IP, I'm shown just outside of Toronto when I'm actually
two hours out), but the chances of showing up in Taiwan when you're in
Tennessee is doubtful.  The point of the matter is that you'll get real
time data in regards to where the user might be located and from there,
you'll get a general idea on when a good time to call is.

There are also bounce VPNs which would make it look like I'm in Texas when
I'm in Toronto.  Depending on how I route my traffic here, I can be
anywhere in the world.


On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:

> Hi everyone,
>
> Wow, such great responses! So my background is not with this type of
> development, so I never really thought about these types of problems
> before. Thank you all for the help!
>
> -will
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi everyone,

Wow, such great responses! So my background is not with this type of
development, so I never really thought about these types of problems
before. Thank you all for the help!

-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Rob Willett]

The problem with doing this is that many IPs addresses are exit IP addreses, 
I.e the ISP or company brings things into their own network and only has a few 
exit nodes, so regardless of where you connect from, everybody comes out of one 
time zone. I know AOL used to do this and I know that IBM does this. 
Disclaimer, I work for IBM and can't access some parts of the BBC website as it 
thinks I'm calling from Germany when I'm in London.  

My own ISP puts my exit node a few hundred miles from where I am and I'm only 
in the UK. 

These databases are best guesses only and so long as you know that them use 
them accordingly. 

Previous suggestions of using the browser or client computer settings seem 
sensible to me.  

-- 
Rob Willett
Sent from my mobile phone


On Thursday, 31 July 2014 at 08:54, Stephen Chrzanowski wrote:

> Looking back at the clarification of what the OP wanted to do, I've got
> this to recommend;
> 
> If your users are talking to your server via the internet and not via a VPN
> connection, instead of relying on what time zone your users browser is
> giving you, look at what IP they're calling in from and do an IP to
> geographical look up to find out where they are. From there you'd be able
> to catalog a 'best time of contact' based on what the Geolocation service
> gives you.
> 
> Doing a quick google search on "ip to geo" I found these two:
> http://www.iplocation.net/ and http://www.geoiptool.com/
> 
> By the looks of it, for a modest yearly fee, you'd be able to download a
> database of IPs to locations and you'd be able to get time zone information
> right from there.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Donald Shepherd]

Speaking as someone who's work routes their internet traffic through a
gateway in Phoenix, AZ despite being based in Australia, guessing time
zones based off IP location is a lot more prone to error than detecting it
based off the client.


On 31 July 2014 17:54, Stephen Chrzanowski  wrote:

> Looking back at the clarification of what the OP wanted to do, I've got
> this to recommend;
>
> If your users are talking to your server via the internet and not via a VPN
> connection, instead of relying on what time zone your users browser is
> giving you, look at what IP they're calling in from and do an IP to
> geographical look up to find out where they are.  From there you'd be able
> to catalog a 'best time of contact' based on what the Geolocation service
> gives you.
>
> Doing a quick google search on "ip to geo" I found these two:
> http://www.iplocation.net/ and http://www.geoiptool.com/
>
> By the looks of it, for a modest yearly fee, you'd be able to download a
> database of IPs to locations and you'd be able to get time zone information
> right from there.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Stephen Chrzanowski]

Looking back at the clarification of what the OP wanted to do, I've got
this to recommend;

If your users are talking to your server via the internet and not via a VPN
connection, instead of relying on what time zone your users browser is
giving you, look at what IP they're calling in from and do an IP to
geographical look up to find out where they are.  From there you'd be able
to catalog a 'best time of contact' based on what the Geolocation service
gives you.

Doing a quick google search on "ip to geo" I found these two:
http://www.iplocation.net/ and http://www.geoiptool.com/

By the looks of it, for a modest yearly fee, you'd be able to download a
database of IPs to locations and you'd be able to get time zone information
right from there.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Roger Binns]

On 30/07/14 10:51, Nico Williams wrote:
> I find that somewhat obnoxious.  I often prefer absolute time

It depends on the content being shown.  We go for human friendly relative
times (eg "13 hours ago") and then have a tooltip that gives the full
timestamp.  Doing maths on times and dates is annoyingly hard, which the
relative value caters for.

The developer/product manager can work out what is most appropriate for
their users - eg showing only relative times, full with tooltip as relative,
both relative and full etc.  But yeah, this is formatting.

Roger

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Nico Williams]

On Wed, Jul 30, 2014 at 12:34 PM, Roger Binns  wrote:
> On 30/07/14 10:05, Nico Williams wrote:
>> Users travel; they don't have a single timezone.  What matters is: the
>> TZ when a user posted / did something, so you can have a vague idea of
>> when they might be sleeping / unavailable.
>
> I'm not sure if you are disagreeing or agreeing with me.

I wasn't either.  But I am now: I'm agreeing with you: timezone needs
to be accounted for as close to the user as possible.  In a web
application, as Simon points out, this should be done in JavaScript on
the page, not on the server.

> There is no need for timezones even for your example.  Displaying the
> timestamp relatively solves that (eg "13 hours ago").

I find that somewhat obnoxious.  I often prefer absolute time and, and
with a clue as to the poster's timezone (e.g., as in e-mail), as that
tells me a lot about the user.  On the other hand, why should I know
that about the user?  It's an information leak they might not like.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Roger Binns]

On 30/07/14 10:05, Nico Williams wrote:
> Users travel; they don't have a single timezone.  What matters is: the
> TZ when a user posted / did something, so you can have a vague idea of
> when they might be sleeping / unavailable.

I'm not sure if you are disagreeing or agreeing with me.

A clearer way of saying it is that I believe timezones are a formatting
issue, and best dealt as close to the user as possible.  For this specific
discussion the user is in front of a browser, so I believe the browser is
the best place to care about the timezones.  Browsers pick up timezone and
formatting preferences from the operating system they run on which in turn
is based on user preferences.

There is no need for timezones even for your example.  Displaying the
timestamp relatively solves that (eg "13 hours ago").

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Simon Slavin]

On 30 Jul 2014, at 6:05pm, Nico Williams  wrote:

> Ideally we'd all just use Zulu time all the time, but that won't fly.

If this is web-facing, the problem is solved.  JavaScript can be told to return 
'now' expressed in UTC.

"The Date.now() method returns the number of milliseconds elapsed since 1 
January 1970 00:00:00 UTC."

If you assume that every user has the timezone set correctly on their computer, 
you just need to trust what JavaScript returns.  A timezone selected by the 
user is used only when displaying dates, or in setting dates in the future.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Nico Williams]

On Wed, Jul 30, 2014 at 11:53 AM, Roger Binns  wrote:
> Why do you even need to store their timezone?  The only time it would matter
> is if you are showing one user what another users local time is.

Users travel; they don't have a single timezone.  What matters is: the
TZ when a user posted / did something, so you can have a vague idea of
when they might be sleeping / unavailable.

In practice asking a user for their timezone every time they login is
obnoxious.  And they might travel without logging out.

Ideally we'd all just use Zulu time all the time, but that won't fly.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Roger Binns]

On 29/07/14 17:23, Will Fong wrote:
> Ah! I have not explained my issue properly :)  I'm very sorry about that.
> 
> I'm using SQLite as a backend to a small website and I have users in
> multiple timezones. When users login, their timezone is retrieved from
> the user table.

Why do you even need to store their timezone?  The only time it would matter
is if you are showing one user what another users local time is.

The way I do this with web stuff is let the browser handle it.  The page is
rendered at the server with some reasonable default (UTC), and then
javascript in the browser updates it for the local timezone and preferences.
 eg:


   2014-07-30 16:43 UTC


The Javascript then replaces the text with "7/30/2014 9:43" and leaves the
tooltip as the UTC time.  We usually make the text more useful - for example
it may say "30 minutes ago", "Tuesday at 2pm", "3 years ago" etc and
automatically update as the page is left open.  This is the library we use,
but there are many out there:

  https://mattbradley.github.io/livestampjs/

This approach means there is no need to store timezones, and that the
formatting (eg 24 hr versus am/pm, timezone) are picked up from their
browser and OS preferences.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Keith Medcalf]

On Tuesday, 29 July, 2014 20:31 Will Fong  said:


>On Wed, Jul 30, 2014 at 10:01 AM, Keith Medcalf 
>wrote:
>> Store and retrieve everything in the database in Zulu time.  Whether
>this means using timestrings, UNIX timestamps, JD or MJD floats is up to
>you.  The application (user interface) is responsible for converting
>retrieved data to the "display timezone" on output and convert data from
>the "input timezone" on input.

>> This is the only reliable way to handle multiple timezones.  There are
>lots of moronic ways and five-nines (asctually more like nine-nines) of
>all software written use those moronic methods and for that reason do not
>work properly ("not work properly" being defined as anything somewhere
>between producing incorrect or ludicrously entertaining results and just
>puking all over the floor).  In many cases "not working" but "not puking"
>is acceptable provided that the moronic behaviour is internally
>consistent.  In others, "not working" is fatal.

>Well, PostgreSQL's method for this to be handled at the
>client/connection level seemed to have worked very well, and doesn't
>seem very "moronic". Having this handled at the database level makes
>the application(s) much less prone to bugs.

PostgreSQL's methods seem well designed (according to the current 
documentation).  It uses a reasonable timezone database (Olsen), allows you to 
specify the "presentation" timezone (the timezone from which timestamps are 
converted on input and to which timestamps are converted on output), stores 
only GMT/UTC, and performs all conversions on "initial" input/"final" output, 
while working entirely in GMT/UTC internally.  

In other words, it does exactly what I said needs to be done at the 
presentation layer within the SQL input/output parser routines.

Many products are far more brain-dead.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Sohail Somani]

On 2014-07-29, 8:23 PM, Will Fong wrote:

Hi,

On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:

'localtime' and 'utc' modifiers.


Ah! I have not explained my issue properly :)  I'm very sorry about that.

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.

Really sorry for the confusion. Late night.


I'd suggest a view on the data that does the conversion. You can also 
use an INSTEAD OF trigger for insertion:


CREATE VIEW user_data_view AS
  SELECT *,to_user_tz(gmt_time,user_tz)
  FROM user_data_view;

When I need to care about timezones, I always store them as GMT and only 
convert them when I need to present them to the user. This makes math 
and comparisons on dates easy.


Fortunately, my applications are usually designed in a manner that there 
are very few lines of code needed to support these to/from conversions.


Sohail


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Nico Williams]

You can haz per-connection TZ setting: use a temp table and join with it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 10:01 AM, Keith Medcalf  wrote:
> Store and retrieve everything in the database in Zulu time.  Whether this 
> means using timestrings, UNIX timestamps, JD or MJD floats is up to you.  The 
> application (user interface) is responsible for converting retrieved data to 
> the "display timezone" on output and convert data from the "input timezone" 
> on input.
>
> This is the only reliable way to handle multiple timezones.  There are lots 
> of moronic ways and five-nines (asctually more like nine-nines) of all 
> software written use those moronic methods and for that reason do not work 
> properly ("not work properly" being defined as anything somewhere between 
> producing incorrect or ludicrously entertaining results and just puking all 
> over the floor).  In many cases "not working" but "not puking" is acceptable 
> provided that the moronic behaviour is internally consistent.  In others, 
> "not working" is fatal.

Well, PostgreSQL's method for this to be handled at the
client/connection level seemed to have worked very well, and doesn't
seem very "moronic". Having this handled at the database level makes
the application(s) much less prone to bugs.


Thanks,
-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Keith Medcalf]

>> 'localtime' and 'utc' modifiers.
>
>Ah! I have not explained my issue properly :)  I'm very sorry about that.
>
>I'm using SQLite as a backend to a small website and I have users in
>multiple timezones. When users login, their timezone is retrieved from
>the user table.
>
>Really sorry for the confusion. Late night.

Store and retrieve everything in the database in Zulu time.  Whether this means 
using timestrings, UNIX timestamps, JD or MJD floats is up to you.  The 
application (user interface) is responsible for converting retrieved data to 
the "display timezone" on output and convert data from the "input timezone" on 
input.

This is the only reliable way to handle multiple timezones.  There are lots of 
moronic ways and five-nines (asctually more like nine-nines) of all software 
written use those moronic methods and for that reason do not work properly 
("not work properly" being defined as anything somewhere between producing 
incorrect or ludicrously entertaining results and just puking all over the 
floor).  In many cases "not working" but "not puking" is acceptable provided 
that the moronic behaviour is internally consistent.  In others, "not working" 
is fatal.

If your "front-end" programming language cannot handle time and timezones 
properly and you need to support multiple timezones, then you have chosen the 
wrong front-end language and/or datetime/timezone handling library.  Stay away 
from anything Microsoft as it is hopeless at dealing with datetime data and 
timezone conversion.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 9:12 AM, Donald Shepherd
 wrote:
> You can represent time zones as integers by using minutes.  Examples: +600
> for AEST, +330 for IST, -480 for PST.  No string manipulation is needed,
> but depending on what or if you're using libraries, you may need extra
> steps in there for convert those values into a representation supported by
> the library.

Ah! I never thought about that. That seems to be the most
straightforward for my use.

It is too bad it's not possible to set this at a query/connection level :(

Thanks!

-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Donald Shepherd]

You can represent time zones as integers by using minutes.  Examples: +600
for AEST, +330 for IST, -480 for PST.  No string manipulation is needed,
but depending on what or if you're using libraries, you may need extra
steps in there for convert those values into a representation supported by
the library.


On 30 July 2014 10:47, Will Fong  wrote:

> Hi,
>
> On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin 
> wrote:
> > Store their timezones in the format "[+-]HH:MM" and apply them by
> appending that text to any dates they provide.  See the "Time Strings"
> section of
>
> I can store each user's timezone setting as "[+-]HH:MM".  But I can
> only apply that to GMT values. So when I'm reading from the database,
> it's a trivial operation.
>
> However, if a user specifies a datetime, I would have to provide the
> reverse of that value to convert the user time into GMT. It would be a
> bit easier (yet still messy) if the timezone was just an integer, then
> I could just "*-1". But the ":MM" seems to make it a messy string
> operation.
>
> Is this the only option? It seems like there would have been a
> "better" way to handle this.
>
> Thanks,
> -will
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Simon Slavin]

On 30 Jul 2014, at 1:47am, Will Fong  wrote:

> On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin  wrote:
>> Store their timezones in the format "[+-]HH:MM" and apply them by appending 
>> that text to any dates they provide.  See the "Time Strings" section of
> 
> I can store each user's timezone setting as "[+-]HH:MM".  But I can
> only apply that to GMT values.

The SQLite routines will apply those timezones to any times.  You just append 
them to the time string you supply to the routines.

> So when I'm reading from the database,
> it's a trivial operation.
> 
> However, if a user specifies a datetime, I would have to provide the
> reverse of that value to convert the user time into GMT. It would be a
> bit easier (yet still messy) if the timezone was just an integer, then
> I could just "*-1". But the ":MM" seems to make it a messy string
> operation.
> 
> Is this the only option? It seems like there would have been a
> "better" way to handle this.

I would probably write a library routine which converted the zone in the form 
the user supplies it (+3, -9, GMT, +0, whatever) to the form I wanted it 
"[+-]HH:MM", and I'd store both of them  /and/ a copy of "[+-]HH:MM" with the 
sign reversed.

Alternatively, you can provide your users with a popup, but deduce all three 
forms from whatever value they picked in the popup.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin  wrote:
> Store their timezones in the format "[+-]HH:MM" and apply them by appending 
> that text to any dates they provide.  See the "Time Strings" section of

I can store each user's timezone setting as "[+-]HH:MM".  But I can
only apply that to GMT values. So when I'm reading from the database,
it's a trivial operation.

However, if a user specifies a datetime, I would have to provide the
reverse of that value to convert the user time into GMT. It would be a
bit easier (yet still messy) if the timezone was just an integer, then
I could just "*-1". But the ":MM" seems to make it a messy string
operation.

Is this the only option? It seems like there would have been a
"better" way to handle this.

Thanks,
-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Simon Slavin]

On 30 Jul 2014, at 1:23am, Will Fong  wrote:

> On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:
>> 'localtime' and 'utc' modifiers.
> 
> Ah! I have not explained my issue properly :)  I'm very sorry about that.
> 
> I'm using SQLite as a backend to a small website and I have users in
> multiple timezones. When users login, their timezone is retrieved from
> the user table.

Store their timezones in the format "[+-]HH:MM" and apply them by appending 
that text to any dates they provide.  See the "Time Strings" section of



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Igor Tandetnik]

On 7/29/2014 8:23 PM, Will Fong wrote:

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.


Well, SQLite delegates to the C runtime for timezone handling. I suspect 
tzset() et al could be used to trick it into assuming a timezone of your 
choice. That's per process though, not per connection.


Alternatively, there is 'N hours' modifier for date/time functions (N 
may be negative). That's much more manual though, as the modifiers will 
have to be written into every query, possibly as bound parameters.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:
> 'localtime' and 'utc' modifiers.

Ah! I have not explained my issue properly :)  I'm very sorry about that.

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.

Really sorry for the confusion. Late night.

-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Igor Tandetnik]

On 7/29/2014 8:10 PM, Will Fong wrote:

On Tue, Jul 29, 2014 at 9:56 PM, Gerry Snyder  wrote:

Have you read http://sqlite.org/lang_datefunc.html   ?


Is there something there that I missed? I didn't see anything there
that relates on how to handle timezone operations.


'localtime' and 'utc' modifiers.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Tue, Jul 29, 2014 at 9:56 PM, Gerry Snyder  wrote:
> Have you read http://sqlite.org/lang_datefunc.html   ?

Yes.

Is there something there that I missed? I didn't see anything there
that relates on how to handle timezone operations.

Thanks,
-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Gerry Snyder]

Have you read http://sqlite.org/lang_datefunc.html   ?

On 7/29/2014 6:41 AM, Will Fong wrote:

Hi,

How are timezones best handled? Since dates are stored in GMT, when I
go to display them, I need to add/subtract the timezone. That's not
too hard when I can just store the timezone as "-5" for EST. When I'm
providing a date to query on, I would have to apply the reverse of the
timezone, "+5", to normalize it to GMT.

That kinda sucks :(

I come from PostgreSQL, so I normally set at the connection level the
timezone and PG handles all the conversions. Does SQLite have a
similar feature?

Is there a standard way to handle this?

Thanks,
-will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users