Re: [sqlite] Why trigger (UPDATE) does not work?

On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote: > On 2018/08/26 2:16 PM, Csányi Pál wrote: > > > >>> When the trigger is fired, those numbers are not updated in the > >>> corresponding fields of the corresponding table. > >>> To be more

Re: [sqlite] Why trigger (UPDATE) does not work?

s'), endDateR from daysTable where resultDate < endDateR ) select days from daysTable where resultDate = endDateR ) WHERE EndDate = NEW.EndDate; END; The question is: why does not work the AllYearsMonthsDays trigger as I expecting? > Regards. > > Brian P Curley > > &

[sqlite] Why trigger (UPDATE) does not work?

ger is fired, those numbers are not updated in the corresponding fields of the corresponding table. To be more precise, the AllYearsMonthsDays trigger does not work. The YearsRemainingMonthsDays trigger works. What am I missing here? -- Best, Pali Üdvözlettel, Csányi Pál ta

[sqlite] Can date('now') fire a trigger automatically after a day passed?

Hi, I have a column in a table where a value in a row is 'now'. The 'now' value is in 'EndDate' column of that row. I have also a trigger which fire up when in the 'EndDate' column a Field was updated. Because the 'now' value does changing every day in sense that every one know that that

Re: [sqlite] Using CTE with date comparison

On Mon, Aug 06, 2018 at 05:02:00PM +, David Raymond wrote: > The whole fencepost thing is probably doing weird things. Here's my take on > it. > This is "time to get from A to B". If you want "Total timespan from A to B > inclusive" then just add 1 day. > > Trying to do the +1 day or -1 day

Re: [sqlite] Using CTE with date comparison

On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote: > > >Some where in the WITH clause above I want to put '+1 day' in the > >command out there. > > That is because the query does not count the StartDate but does count the > EndDate, so if your EndDate is the next day from the

Re: [sqlite] Using CTE with date comparison

2018-08-05 0:18 GMT+02:00 Keith Medcalf : > > WITH RECURSIVE > dates(dateD) AS (VALUES(:StartDate) > UNION ALL > SELECT date(dateD, '+1 year') > FROM dates >WHERE date(dateD, '+1 year') <= :EndDate > ) > SELECT

Re: [sqlite] Using CTE with date comparison

2018-08-04 14:07 GMT+02:00 Csányi Pál : > 2018-08-03 13:09 GMT+02:00 R Smith : > >> Do you want a recursive query that will add all years between 3 October and >> some other date, but NOT the first year and NOT the last year? > > I want a recursive query that gives years

Re: [sqlite] Using CTE with date comparison

2018-08-03 13:09 GMT+02:00 R Smith : > Do you want a recursive query that will add all years between 3 October and > some other date, but NOT the first year and NOT the last year? I want a recursive query that gives years to the start date until it reaches the end date or if it exceeds the end

Re: [sqlite] Using CTE with date comparison

2018-08-03 13:09 GMT+02:00 R Smith : > > > On 2018/08/03 12:35 PM, Csányi Pál wrote: >> >> 2018-08-02 23:12 GMT+02:00 R Smith : >>> >>> On 2018/08/02 10:29 PM, Csányi Pál wrote: >>>> >>>> Hi, >>>> >>>&

Re: [sqlite] Using CTE with date comparison

2018-08-02 23:12 GMT+02:00 R Smith : > On 2018/08/02 10:29 PM, Csányi Pál wrote: >> >> Hi, >> >> I just want to know why the following SQLite query does not work as I >> expected? >> >> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION AL

Re: [sqlite] Using CTE with date comparison

2018-08-02 23:12 GMT+02:00 R Smith : > On 2018/08/02 10:29 PM, Csányi Pál wrote: >> >> Hi, >> >> I just want to know why the following SQLite query does not work as I >> expected? >> >> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION AL

[sqlite] Using CTE with date comparison

Hi, I just want to know why the following SQLite query does not work as I expected? WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD) <= date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates; 2017-10-03|3 which is

Re: [sqlite] Understanding SELECT statement

Once again, thank you all very much for your help. I understand now this query. R Smith ezt írta (időpont: 2018. jún. 26., K, 17:28): > > > On 2018/06/26 4:42 PM, Csányi Pál wrote: > > > >>> Then I get help and this code: > >>> INSERT INTO SchoolYear

Re: [sqlite] Understanding SELECT statement

Igor Tandetnik ezt írta (időpont: 2018. jún. 26., K, 16:10): > > On 6/26/2018 9:15 AM, Csányi Pál wrote: > > Then I get help and this code: > > INSERT INTO SchoolYearTeachingDays > > SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT >

Re: [sqlite] [EXTERNAL] Understanding SELECT statement

esult row of the SELECT exactly once. If you want to insert duplicated rows > of the SELECT only once, you need to SELECT DISTINCT. > > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Csányi P

[sqlite] Understanding SELECT statement

Hi, I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have

Re: [sqlite] Free tool to visualize a schema?

2018-04-15 15:49 GMT+02:00 Lifepillar <lifepil...@lifepillar.me>: > On 15/04/2018 14:14, Csányi Pál wrote: >> >> 2018-04-15 14:08 GMT+02:00 R Smith <ryansmit...@gmail.com>: >>> >>> On 2018/04/15 11:52 AM, Csányi Pál wrote: >>>> >>&g

Re: [sqlite] Free tool to visualize a schema?

2018-04-15 14:08 GMT+02:00 R Smith <ryansmit...@gmail.com>: > On 2018/04/15 11:52 AM, Csányi Pál wrote: >> >> Hi, >> >> I think the visualization of a schema helps to develop a sqlite database. >> >> I am searching for a free software, like SchemaCraw

[sqlite] Free tool to visualize a schema?

Hi, I think the visualization of a schema helps to develop a sqlite database. I am searching for a free software, like SchemaCrawler. It is good, but can't show CONSTRAINT and FOREIGN KEY names in the diagram. I just tried out the Sqleton but it can't be start on my system so far. Do you uses

Re: [sqlite] SELECT with CASE

2018-04-12 21:09 GMT+02:00 Csányi Pál <csanyi...@gmail.com>: > Thank you very much for the help and for the explanations. > > Waw! It is so complicated at first! I hope I shall understand these soon. > > Finally I decide to use this query: > SELECT Keltezes FROM Orak WHE

Re: [sqlite] SELECT with CASE

Thank you very much for the help and for the explanations. Waw! It is so complicated at first! I hope I shall understand these soon. Finally I decide to use this query: SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime') ORDER BY Keltezes LIMIT 1; At last this works on my Gentoo

Re: [sqlite] SELECT with CASE

Hi Ryan, 2018-04-12 17:36 GMT+02:00 R Smith <ryansmit...@gmail.com>: > On 2018/04/12 5:20 PM, Csányi Pál wrote: >> >> Yes, this is what I am asking. >> >> 2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>: >>> >>> Which s

Re: [sqlite] SELECT with CASE

; >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >>Sent: Thursday, 12 April, 2018 09:20 >>To: SQLite mailing list >>Subject: Re: [sqlite] SELECT with CASE >> >>Yes,

Re: [sqlite] SELECT with CASE

he current concept of 'tomorrow' on > the computer on which the query is run." > > Is this what you are asking? > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Mes

Re: [sqlite] SELECT with CASE

2018-04-12 17:08 GMT+02:00 Keith Medcalf : > > select TheDate from Dates where TheDate == date('now'); Yes, but I want the CASE because if there is no such date in the Dates table which is equal to the date('now') then it should return the date('now','+1 day').

Re: [sqlite] SELECT with CASE

2018-04-12 17:00 GMT+02:00 Peter Da Silva : > One of the lines of the output does indeed have '2018-04-12' as expected. Indeed, I did not notice. Then how can I get only that date from the Dates table - which is equal to the current date?

Re: [sqlite] SELECT with CASE

2018-04-12 16:51 GMT+02:00 Peter Da Silva : > You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is > what you're getting, no? Yes, indeed. But I thought the first part would be done: CASE TheDate WHEN date('now') THEN TheDate that is, if the

Re: [sqlite] SELECT with CASE

Hi Simon, 2018-04-12 14:32 GMT+02:00 Simon Slavin <slav...@bigfraud.org>: > On 12 Apr 2018, at 1:25pm, Csányi Pál <csanyi...@gmail.com> wrote: > >> SELECT CASE TheDate = date('now') WHEN TheDate ... > > I don't think that's what you wanted. Perhaps > >

[sqlite] SELECT with CASE

Hi, I have a small database: DatesOfYear.db with only one table: CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL); I insert into the table some datas with: INSERT INTO Dates VALUES(1,'2018-04-01'); INSERT INTO Dates VALUES(2,'2018-04-02'); INSERT INTO Dates

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

2018-03-23 21:52 GMT+01:00 David Raymond : > This gets a little ugly. Was stuck for a while wondering what the heck was > going on until I found out that the modulo operator can return negatives. > Which makes absolutely no sense coming from someone who was a math

[sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

Hi, is it possible to create a table from other tables with the following conditions? Note! My goal is to create that table 'Hours' with a say 362 records and after that to update those records with UPDATE sql commands. That is, I don't want to INSERT in that table any more records, but just

[sqlite] Foreign keys - one to one, one to many relationship

Hi, on Gentoo linux I have installed sqlite3 version 3.19.3. With which constraints can one create a foreign key to first: get one to one relationship, and second: one to many relationship? -- Best, Pali ___ sqlite-users mailing list

[sqlite] Support for named foreign keys?

Hi, when will SQLite support the named foreign keys? I am using SchemaCrawler to visualize my SQLite databases and because SQLite does not support it yet, foreign keys get automatically generated foreign key names in the diagram. So it would be nice if SQLite would support it one day. Is'n it?

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

Hello Simon, 2017-08-28 15:28 GMT+02:00 Simon Slavin <slav...@bigfraud.org>: > > On 28 Aug 2017, at 8:13am, Csányi Pál <csanyi...@gmail.com> wrote: > >> I attach in this mail the Probe.db file to ask you to see it, whether >> is it really the database, or just a

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

2017-08-28 8:40 GMT+02:00 Simon Slavin : > > > On 28 Aug 2017, at 7:37am, J Decker wrote: > >> is redirection so hard? >> >> sqlite3 database.db < database.commands > > OP is learning and testing. If redirection doesn’t work, they don’t know > what’s

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

Hello Simon, 2017-08-28 7:14 GMT+02:00 Simon Slavin <slav...@bigfraud.org>: > > > On 28 Aug 2017, at 5:50am, Csányi Pál <csanyi...@gmail.com> wrote: > >> When I am at sqlite> prompt, I enter SQL commands to create tables for >> the datab

[sqlite] Converting an sqlite database into format that an Android app can uses?

Hi, I am using sqlite3 on a command line, in an xterm window on Gentoo linux system. When I am at sqlite> prompt, I enter SQL commands to create tables for the database Probe.db which is a file on my disk. In the Probe.db file are those SQL commands which I just entered and naturally these line