Re: [sqlite] Question about date & time

[William Drago]

Thanks for the reply. I understand. I am going to do some 
experimenting just to make sure...


Regards,
-Bill

On 9/15/2013 3:13 PM, Petite Abeille wrote:

On Sep 15, 2013, at 8:31 PM, William Drago  wrote:


Thanks for the reply. Seconds since the epoch does make a good timestamp. Is 
that what is normally used to extract data between time periods?

(Date & Time seems to be a popular topic at the moment)

There is nothing prescriptive in using epoch time.

As SQLite doesn't have a dedicated date type, you are free to decide how you 
want to handle it.

There are two main encoding:

(1) As a number: Julian date, unix epoch, etc
(2) As a string: ISO 8601 & co.. Just make sure that your string representation 
sorts properly.

http://www.sqlite.org/lang_datefunc.html

The granularity of the date is up to you as well: day, hour, milliseconds, etc. 
This is more driven by what's convenient for your application. Ditto if this should 
be split between date &  time.

Depending on the task at hand, you could even require a much more full fledge 
set of entities:

   create table if not exists date
   (
 idinteger not null constraint date_pk primary key,

 year  integer not null,
 month integer not null,
 day   integer not null,

 day_of_year   integer not null,
 day_of_week   integer not null,
 week_of_year  integer not null,

 constraintdate_uk unique( year, month, day )
   )

   create table if not exists time
   (
 id  integer not null constraint time_pk primary key,

 hourinteger not null,
 minute  integer not null,
 second  integer not null,

 constraint  time_uk unique( hour, minute, second )
   )

And then there are timezones, etc…



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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13





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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Simon Slavin]

On 17 Sep 2013, at 12:32am, James K. Lowden  wrote:

> I suggest that when the observed behavior is known to be at variance
> with what is specified in the SQL standard, it should be viewed as a
> defect and as a candidate for revision.  If it also fails on
> theoretical grounds -- as when atomicity is promised but not supplied
> -- it should be added to the list of known bugs until it's rectified.  

Presumably it should be added to



or something like it.

Actually, while I feel that this is a problem with respect to CURRENT_TIME, 
etc., since Kees pointed out that this is defined in the SQL standard, I have 
no real problem with current behaviour when 'now' is used with the datetime 
functions.  Unless someone finds that /that/ is in the standard too.

Wonder whether it can be fixed in SQLite4.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[James K. Lowden]

On Mon, 16 Sep 2013 10:38:03 -0400
Richard Hipp  wrote:

> one can easily imagine that one or
> more of those two million applications does something like this:
> 
>  SELECT current_timestamp, * FROM tab;
> 
> And then runs sqlite3_step() every five or ten seconds in a background
> process to fetch a new row, and expects the timestamp on each row to
> reflect the actual time of retrieval from disk. 

It is easy to imagine.  It's also broken by design.  The simple and
correct way to get new data is to issue a new query.  Something that
runs every 5 or 10 seconds to return a single row isn't going to suffer
from re-executing the query.  

Quite often when someone on this list suggest a feature, the answer is
there are N million applications using SQLite and a nonzero
probability of breaking one if current behavior changes.  

The question arises: under what circumstances should the behavior
change anyway?  Or must every error be defended as a feature?  

While it's true that something might break, it's also unlikely the
current feature set is pareto-optimal.  That is, there are would-be
users who don't choose SQLite because of missing/broken features, and
there are existing users who are forced to know about and work around
problems that might be relied on by others.  

I suggest that when the observed behavior is known to be at variance
with what is specified in the SQL standard, it should be viewed as a
defect and as a candidate for revision.  If it also fails on
theoretical grounds -- as when atomicity is promised but not supplied
-- it should be added to the list of known bugs until it's rectified.  

Application of such a standard would guide SQLite in the direction of
standards compliance and formal correctness.  I cannot but think that
would benefit all its users, present and future.  

--jkl


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[James K. Lowden]

On Sat, 14 Sep 2013 17:19:22 +0400
Yuriy Kaminskiy  wrote:

> > Notwithstanding the timezone you want to use, 
> > 
> > explain select * 
> >   from entry 
> >  where bankdate >= date('now', 'start of month') 
> >and bankdate < date('now', 'start of month', '-1 day');
> > 
> > will generate the following code:
> 
> And there are another pitfall in this query: date('now') is *RACY*.
> That is, if you execute this query *exactly* at midnight at month
> boundary, first and second DATE() invocation may refer to *different*
> months.
> 
> And if your query return several rows, and there will be month
> boundary between sqlite3_step(), your query also can return data from
> two month.

That's a disappointment, and nonstandard behavior.  It means SELECT is
not atomic.  

The notion of "now" should not change while the query is being
processed.  "date('now', 'start of month')" should return the same
value no matter how many times it appears it the query, and no matter
how long it takes to process the results.  

--jkl

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

Re: [sqlite] Select with dates

[Petite Abeille]

On Sep 16, 2013, at 10:48 AM, Niall O'Reilly  wrote:

>   Consecutive closed intervals overlap.  Depending on the
>   application, this may be a problem; it can be avoided by
>   using half-open ones.

What about simply using not overlapping intervals and call it a day?

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Jean-Christophe Deschamps]

There are other ways to get a consistent 'now' across an entire SQL
statement:


Of course!

This issue is sufficiently obscure that it is not worth adding (and 
testing

and documenting and supporting forever) yet another pragma.


I personally wouldn't call it so "obscure" as it pops up regularly with 
untold consequences in a scripting language I use, but it's part of the 
programmer's responsability after all. Not a deal breaker anyway.


--
JcD 


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 12:37 PM, Jean-Christophe Deschamps <
j...@antichoc.net> wrote:

> I personally wouldn't call it so "obscure" as it pops up regularly with
> untold consequences in a scripting language I use, but it's part of the
> programmer's responsability after all. Not a deal breaker anyway.
>
>
I call it "obscure" because the date and time functions have worked the way
they do for a least 10 years and last week was the first time I have heard
any complaints.  Surely if the behavior were causing any significant
problems for anyone I would have heard about it long before.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 11:14 AM, Jean-Christophe Deschamps <
j...@antichoc.net> wrote:

> Perhaps a new connection-wide "pragma freeze_now=ON;" which would freeze
> 'now' at the beginning of the next statement and unfreeze it at the next
> auto-commit or when processing a "pragma freeze_now=OFF;" would solve
> another part of the race issue.
>

There are other ways to get a consistent 'now' across an entire SQL
statement:

(1) Run "SELECT julianday('now')" separately, then bind the result into
your SQL statement in place of the 'now' strings.
(2) Enclose the date and time functions in a subquery:  SELECT (SELECT
timestamp('now')), * FROM tab;

This issue is sufficiently obscure that it is not worth adding (and testing
and documenting and supporting forever) yet another pragma.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Jean-Christophe Deschamps]

There are perhaps 2 million applications in the wild that use SQLite, 
so it
will be difficult to check them all. But one can easily imagine that 
one or

more of those two million applications does something like this:

 SELECT current_timestamp, * FROM tab;

And then runs sqlite3_step() every five or ten seconds in a background
process to fetch a new row, and expects the timestamp on each row to
reflect the actual time of retrieval from disk.  Causing 'now' to mean
exactly the same time for an entire SQL statement would break such
applications.

As a compromise, the current SQLite trunk causes 'now' to be exactly the
same for all date and time functions within a single sqlite3_step() call.


Perhaps a new connection-wide "pragma freeze_now=ON;" which would 
freeze 'now' at the beginning of the next statement and unfreeze it at 
the next auto-commit or when processing a "pragma freeze_now=OFF;" 
would solve another part of the race issue.


Applications relying on the old behavior don't change and new ones 
where the possibility of race is dangerous can avoid it at little cost.


Still some multi-threaded apps would need to consider the implications, 
but we all know what you Richard think about threads!


--
JcD 


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson
wrote:

> Rhetorical question: if sqlite3's behavior were tightened up would anybody
> complain? Is there any possible use case where replacing the current
> random-ish behavior with something consistent would change an application?
> Seems like the requested behavior happens on accident often enough that no
> current application could rely on its failure to appear.
>

There are perhaps 2 million applications in the wild that use SQLite, so it
will be difficult to check them all. But one can easily imagine that one or
more of those two million applications does something like this:

 SELECT current_timestamp, * FROM tab;

And then runs sqlite3_step() every five or ten seconds in a background
process to fetch a new row, and expects the timestamp on each row to
reflect the actual time of retrieval from disk.  Causing 'now' to mean
exactly the same time for an entire SQL statement would break such
applications.

As a compromise, the current SQLite trunk causes 'now' to be exactly the
same for all date and time functions within a single sqlite3_step() call.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Ryan Johnson]

On 15/09/2013 2:23 PM, Yuriy Kaminskiy wrote:

Stephan Beal wrote:

On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy  wrote:


Sure, there can be several way to interpret CURRENT_* and *('now').
However,
some of them can be useful (transaction, statement), and others (step) -
cannot
be. And some (sub-expression, the way it "works" currently) are purely
insane.


i've been following this list since 2006 or 2007 and i can't remember

Oh, yes, yes, "I was on debian [...] list since [...] and can't remember anyone
complaining about broken RNG for two years". So what?

And, by the way, I already complained about this behavior on this list in the
beginning of 2012 year.


anyone every complaining about the current behaviour before. If the
behaviour bothers you, use a user-defined function which provides the

It does not bother *me* - I can happily live with knowledge that SQLite
CURRENT_*/*('now') is broken by design and should not be used ever. It should
bother people that use sqlite for something serious.
Rhetorical question: if sqlite3's behavior were tightened up would 
anybody complain? Is there any possible use case where replacing the 
current random-ish behavior with something consistent would change an 
application? Seems like the requested behavior happens on accident often 
enough that no current application could rely on its failure to appear.


$0.02
Ryan

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Ryan Johnson]

On 15/09/2013 3:36 AM, Petite Abeille wrote:

On Sep 15, 2013, at 12:53 AM, Kees Nuyt  wrote:


3) If an SQL-statement generally contains more than one reference
   to one or more s, then all such ref-
   erences are effectively evaluated simultaneously.

FWIW, Oracle concurs:

"All of the datetime functions that return current system datetime information, such 
as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each 
SQL statement, regardless how many times they are referenced in that statement."
I'm pretty sure that anything weaker breaks [the illusion of] 
serializability, by giving users a way to see that their transactions 
did not execute in the order they appeared to:


A: select CURRENT_TIMESTAMP; update foo set v=1 where k=0; select * from 
foo;
B: update foo set v=2 where k=0; select * from foo; select 
CURRENT_TIMESTAMP;


Whether that matters in practice, I wouldn't know...

Ryan

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

Re: [sqlite] quetion on editing a sqlite database

[Simon Slavin]

On 15 Sep 2013, at 11:30pm, john white  wrote:

> Oh, there is a location column with this
> information.  I can do them, one at a time, but that is going to take a LOT
> of time.  I was hoping there would be a way to select them all and then
> simply do a find and replace thing.  
> 
> An example would be: "C:\TV\" which should now be '/TV/".  Even better would
> be to change all the "\" to "/" and then just delete the leading "C:".  

Use the SQLite shell tool for one of your platforms, or perhaps write your own 
program to open the databases and execute these two lines.

UPDATE myTable SET location = replace(location, '\', '/')
UPDATE myTable SET location = replace(location, 'C:', '')

While you're doing that, you might think hard about what'll happen if you ever 
need to use the same database on multiple platforms.  While you're designing 
the changes it might be worth making two different columns, one for the 
location under Windows, and the other for the location under Linux.

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

Re: [sqlite] quetion on editing a sqlite database

[Tony Papadimitriou]

See the REPLACE(x,y,z) function.  Use UPDATE table SET field = 
REPLACE(field,)


-Original Message- 
From: john white

Sent: Monday, September 16, 2013 1:30 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] quetion on editing a sqlite database

I have a bit of a problem.  I am moving a program (sickbeard) from windows 
to

ubuntu.  The program has an sqlite database.  It lists a variety of things
with their physical location on disk.  The problem is that ubuntu (linux)
location information is slightly different between windows and ubuntu so I
must change those locations.  Oh, there is a location column with this
information.  I can do them, one at a time, but that is going to take a LOT
of time.  I was hoping there would be a way to select them all and then
simply do a find and replace thing.

An example would be: "C:\TV\" which should now be '/TV/".  Even better would
be to change all the "\" to "/" and then just delete the leading "C:".

Thank you...

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

Re: [sqlite] quetion on editing a sqlite database

[Stephan Beal]

On Mon, Sep 16, 2013 at 12:30 AM, john white  wrote:

> An example would be: "C:\TV\" which should now be '/TV/".  Even better
> would
> be to change all the "\" to "/" and then just delete the leading "C:".
>

The first solution i would try (thought possibly not the best solution!)
would be adding a custom user function which does such rewriting for me,
e.g.:

select mangle_filename(foo) from bar...

where mangle_filename would:

a) if string[1]==':', skip first 2 bytes, or replace with /C/..., or
similar.
b) replace \ with / globally
c) return the adjusted string.

or some such. This can be done without touching your core app code, only
the display code and related queries.


(That said - i would have normalized to Unix paths in the first place
because that's my primary environment ;)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] quetion on editing a sqlite database

[LMHmedchem]

john white [via SQLite] wrote:
> I have a bit of a problem.  I am moving a program (sickbeard) from
> windows to ubuntu.  The program has an sqlite database.  It lists a
> variety of things with their physical location on disk.  The problem is
> that ubuntu (linux) location information is slightly different between
> windows and ubuntu so I must change those locations.  Oh, there is a
> location column with this information.  I can do them, one at a time,
> but that is going to take a LOT of time.  I was hoping there would be a
> way to select them all and then simply do a find and replace thing.
>
> An example would be: "C:\TV\" which should now be '/TV/".  Even better
> would be to change all the "\" to "/" and then just delete the leading
> "C:".
>
> Thank you...
>
> 
> If you reply to this email, your message will be added to the discussion
> below:
> http://sqlite.1065341.n5.nabble.com/quetion-on-editing-a-sqlite-database-tp71297.html
>
> To unsubscribe from SQLite, click here
> .
> NAML
> 
>

You might try to export the database file to something like csv. One you 
have it in a text file format, you could do things like find and replace 
in a text editor or excel. Once you have things sorted, you could 
re-import the csv into SQLite. The Firefox SQLite manager lets you 
export a table from a database and also to import one, so that might 
work for you.

LMH






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/quetion-on-editing-a-sqlite-database-tp71297p71298.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] quetion on editing a sqlite database

[john white]

I have a bit of a problem.  I am moving a program (sickbeard) from windows to
ubuntu.  The program has an sqlite database.  It lists a variety of things
with their physical location on disk.  The problem is that ubuntu (linux)
location information is slightly different between windows and ubuntu so I
must change those locations.  Oh, there is a location column with this
information.  I can do them, one at a time, but that is going to take a LOT
of time.  I was hoping there would be a way to select them all and then
simply do a find and replace thing.  

An example would be: "C:\TV\" which should now be '/TV/".  Even better would
be to change all the "\" to "/" and then just delete the leading "C:".  

Thank you...



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/quetion-on-editing-a-sqlite-database-tp71297.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[Simon Slavin]

On 16 Sep 2013, at 9:48am, Niall O'Reilly  wrote:

> On 14 Sep 2013, at 18:09, Petite Abeille wrote:
> 
>> Yeah... not sure why people are doing that to themselves though :D
> 
>   Consecutive closed intervals overlap.  Depending on the
>   application, this may be a problem; it can be avoided by
>   using half-open ones.

Or by not relying on a continuous number line when what you really want is 
serial-numbering.

I wouldn't attempt to locate follow-chains inside a SELECT which was doing 
something else.  I'd write one routine to figure out the serial numbers (and 
possibly store them back in the tables), and once that was done, use those to 
do the task I was really trying to do.

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

Re: [sqlite] Select with dates

[Niall O'Reilly]

On 14 Sep 2013, at 18:09, Petite Abeille wrote:

> Yeah... not sure why people are doing that to themselves though :D

Consecutive closed intervals overlap.  Depending on the
application, this may be a problem; it can be avoided by
using half-open ones.

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