Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

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

Re: [sqlite] Handling Timezones

>> '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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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,

Re: [sqlite] Handling Timezones

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.

Re: [sqlite] Handling Timezones

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

Re: [sqlite] Handling Timezones

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

[sqlite] Handling Timezones

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,