[sqlite] Better way to get range of dates

When getting data between a range of dates you can use: WHERE date >= DATE('now', '-7 days') AND date < DATE('now') or: WHERE date BETWEEN DATE('now', '-7 days') AND DATE('now', '-1 days') Is there a preferred way? In a way I like the second

Re: [sqlite] sqlite on IBM z/OS Unix

I am not familiar with the internals of z/OS ... YMMV. My initial take would be that it would depend on whether the LE remains active (initialized) and maintains its memory allocations/file opens, etc, between invocations from your native z/OS assembly code. That is to say is the sequence:

Re: [sqlite] sqlite on IBM z/OS Unix

It is not clear to me why your program closes the sqlite3 database at the end of each C function invocation. AFAIK sqlite3 should not close any database connection unless your program invokes sqlite3_close. If something in the "Language Environment" closes and releases internal resources at the

[sqlite] sqlite on IBM z/OS Unix

Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now looking at a c program to make SQL calls to this. Starting with an Assembler routine that runs within z/OS (not Unix), which is not LE (Language Environment)enabled, we then call a c routine (numerous times) that is LE

Re: [sqlite] Get data in one query

Julian Day is defined in terms of Universal Time. https://en.wikipedia.org/wiki/Julian_day 2018-08-29 22:33 GMT+02:00, David Raymond : > Good to know. Is that actually documented anywhere? All I see is... > > > https://www.sqlite.org/lang_datefunc.html > "Format 11, the string 'now', is

Re: [sqlite] Get data in one query

Good to know. Is that actually documented anywhere? All I see is... https://www.sqlite.org/lang_datefunc.html "Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use."

Re: [sqlite] Get data in one query

2018-08-29 21:44 GMT+02:00 Keith Medcalf : > > ... don't forget that Date('now') returns the UT1 date, not the local (as > in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local > date in accordance with the timezone where your computer thinks it is > located and should always

Re: [sqlite] Get data in one query

2018-08-29 21:26 GMT+02:00 Wout Mertens : > By the way, why not store the time as epoch? Date and time in one... > Because I think it is better to have date and time as different (text) fields. -- Cecil Westerhof ___ sqlite-users mailing list

Re: [sqlite] Get data in one query

... don't forget that Date('now') returns the UT1 date, not the local (as in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in accordance with the timezone where your computer thinks it is located and should always be accurate for 'now' but maybe not a few years in

Re: [sqlite] Get data in one query

By the way, why not store the time as epoch? Date and time in one... On Wed, Aug 29, 2018, 8:46 PM Cecil Westerhof wrote: > 2018-08-29 18:58 GMT+02:00 Cecil Westerhof : > > > 2018-08-29 18:06 GMT+02:00 R Smith : > > > >> > >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late > >> FROM (SELECT 1 AS

Re: [sqlite] Get data in one query

2018-08-29 18:58 GMT+02:00 Cecil Westerhof : > 2018-08-29 18:06 GMT+02:00 R Smith : > >> >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late >> FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late >> FROM messages >> WHERE date = DATE('now') >>) >> > > Works like a charm.

Re: [sqlite] Pragma Use for TCL scripts

The data was encrypted by a third party softward, which I assume does have that since it works with their application. I know it was possible to extract that data directly from the encrypted databases as long as you provided the encryption key as the community for this software talked about

Re: [sqlite] Get data in one query

2018-08-29 18:06 GMT+02:00 R Smith : > > SELECT SUM(Tot) AS Tot, SUM(Late) AS Late > FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late > FROM messages > WHERE date = DATE('now') >) > Works like a charm. Thanks. I made it even more useful: SELECT Total ,

Re: [sqlite] Pragma Use for TCL scripts

On 8/29/18, Tilsley, Jerry M. wrote: > > Trying to access some encrypted databases via TCL interface and need to > provide the key. Can someone provide an good example of this? I've found > some things on google, but not having much success. > Do you have a license for the SQLite Encryption

Re: [sqlite] Get data in one query

SELECT SUM(Tot) AS Tot, SUM(Late) AS Late   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late           FROM messages          WHERE date = DATE('now')    ) On 2018/08/29 5:56 PM, Cecil Westerhof wrote: I have a table messages in which something is put every minute. The total

Re: [sqlite] Get data in one query

On 29 Aug 2018, at 4:59pm, Simon Slavin wrote: > You can use this structure: > > SELECT (first SELECT), (second SELECT) Better still in your case, you can use SELECT (first SELECT) as Total, (second SELECT) as Late Simon. ___ sqlite-users

[sqlite] Pragma Use for TCL scripts

All, Trying to access some encrypted databases via TCL interface and need to provide the key. Can someone provide an good example of this? I've found some things on google, but not having much success. Thanks, Jerry Tilsley Disclaimer Confidentiality

Re: [sqlite] Get data in one query

On 29 Aug 2018, at 4:56pm, Cecil Westerhof wrote: > Is there a way to get this information in one query? You can use this structure: SELECT (first SELECT), (second SELECT) Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Get data in one query

I have a table messages in which something is put every minute. The total messages that are added today I can get with: SELECT COUNT(*) AS Total FROM messages WHERE date = DATE('now') And the number of messages that where entered today, but not at the start of a minute I can get

[sqlite] SQLite with branching

I have no connection with the following project. Described poorly on the web site so here's my own description: This is an extension of SQLite which allows branched versions, each new branch creating one dataset which existed before the new branch and a

Re: [sqlite] SQLite name/identifier length limit?

On Wed, Aug 29, 2018 at 12:09 PM Richard Hipp wrote: > On 8/29/18, Dominique Devienne wrote: > > https://www.sqlite.org/limits.html doesn't say explicitly. > > Unlimited? SQLITE_MAX_LENGTH ? > > > > PostgreSQL is limited to 63 chars. > > Oracle is limited to 30 and 128 chars (latter > 12.2). >

Re: [sqlite] SQLite name/identifier length limit?

On 8/29/18, Dominique Devienne wrote: > https://www.sqlite.org/limits.html doesn't say explicitly. > Unlimited? SQLITE_MAX_LENGTH ? > > PostgreSQL is limited to 63 chars. > Oracle is limited to 30 and 128 chars (latter > 12.2). > Other DBs? (just curious). --DD There is no arbitrary limit, other

[sqlite] SQLite name/identifier length limit?

https://www.sqlite.org/limits.html doesn't say explicitly. Unlimited? SQLITE_MAX_LENGTH ? PostgreSQL is limited to 63 chars. Oracle is limited to 30 and 128 chars (latter > 12.2). Other DBs? (just curious). --DD ___ sqlite-users mailing list

Re: [sqlite] Shared memory cache files on disk?

On Wed, Aug 29, 2018 at 1:11 AM Keith Medcalf wrote: > SQLITE_USE_URI > > If this is not defined then URI's are not parsed. > > https://www.sqlite.org/uri.html > Thank you, that helps a lot! ___ sqlite-users mailing list