[sqlite] Date data type

Hi, How can I manage date and time using sqlite? [Do I have to do it outside sqlite?] Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to

Re: [sqlite] Date data type

hi, i use numeric values and the sqlite date-functions. example: create table if not exists online (ip integer primary key, date number) insert or replace into online values (12345, julianday('now')) select id, datetime(date, 'localtime') date from online order by date desc limit 100

Re: [sqlite] Date data type

Lloyd wrote: Hi, How can I manage date and time using sqlite? [Do I have to do it outside sqlite?] http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases.

Re: [sqlite] Date data type

Thanks gg and Craig Morrison for your informative reply. I would like to know one more thing, can I use these date and time functions with comparison operators? Will they return the correct result or as per the string comparison rules? Thanks again, Lloyd. On Tue, 2006-11-07 at 04:25 -0500,

[sqlite] Q about new SQLite API

I'm working on a new API routine for SQLite and I have questions for the community. The working name of the new api is sqlite3_prepare_v2(). sqlite3_prepare_v2() works like sqlite3_prepare() in that it generates a prepared statement in an sqlite3_stmt structure. The differences is in the

Re: [sqlite] Loading a table into memory

I am trying for loading only that table one into memory. Beacuse in my DB I have two or more three table which are quite large and I don't want to waste memory. Is it possible that way. ... Chetana. - Original Message From: Jay Sprenkle [EMAIL PROTECTED] To: sqlite-users@sqlite.org

RE: [sqlite] Q about new SQLite API

Sounds great, it would certainly clean up a lot of my code :) 1: How about sqlite3_prepare_statement() 2: Not really, most of my suggestions will be for virtual table error handling 3: I vote for SQLITE_SCHEMA. Thanks! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

Re: [sqlite] Q about new SQLite API

2006/11/7, [EMAIL PROTECTED] [EMAIL PROTECTED]: QUESTION 1: sqlite3_prepare_ex (or sqlite3_compile) QUESTION 3: maybe: SQLITE_INVALID_SQL Btw. are there plans to implement grouping operator (i.e. parentheses) and a real NOT (not just exclude) features in Fts Two ? Best regards,

Re: [sqlite] Q about new SQLite API

[EMAIL PROTECTED] wrote: QUESTION 3: Suppose there is a schema change and the SQL statement is automatically reprepared. But the schema change is such that the SQL is no longer valid. (Perhaps one of the tables mentioned in a SELECT statement was dropped.) What error code should sqlite3_step()

Re: [sqlite] Q about new SQLite API

1. sqlite3_re_prepare or simply sqlite3_reprepare On 11/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? Some possibilities include: sqlite3_prepare_ex1

Re: [sqlite] Q about new SQLite API

This is a very good news: 1. sqlite3_compile 2. maybe with the new sqlite3_compile routine there should also be a way to retrieve the rowid of the current row (NULL is no valid rowid is found), a possible API could be sqlite3_rowid to call after each sqlite3_step only if it returns an

Re: [sqlite] Q about new SQLite API

[EMAIL PROTECTED] writes: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? Some possibilities include: sqlite3_prepare_ex1 sqlite3_prepare_ng sqlite3_new_prepare sqlite3_compile sqlite3_compile works

Re: [sqlite] Q about new SQLite API

Klemens Friedl [EMAIL PROTECTED] wrote: Btw. are there plans to implement grouping operator (i.e. parentheses) and a real NOT (not just exclude) features in Fts Two ? Maybe and no. -- D. Richard Hipp [EMAIL PROTECTED]

[sqlite] Re: Q about new SQLite API

Marco Bambini [EMAIL PROTECTED] wrote: 2. maybe with the new sqlite3_compile routine there should also be a way to retrieve the rowid of the current row (NULL is no valid rowid is found), a possible API could be sqlite3_rowid to call after each sqlite3_step only if it returns an SQLITE_ROW is

RE: [sqlite] Q about new SQLite API

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 07, 2006 6:18 AM To: sqlite-users@sqlite.org Subject: [sqlite] Q about new SQLite API I'm working on a new API routine for SQLite and I have questions for the community. [snip] QUESTION

Re: [sqlite] Re: Q about new SQLite API

I know that there are many times where a rowid could not be returned (this is why I suggested to return NULL or -1 for example). A lot of times we don't have access to the sql issued by end users and a way to retrieve the rowid (if there is one) could simplify a lot all our code (and I am

[sqlite] Retrieving date

Hi, I stored a unix epoch (32 bit integer) date in the sqlite data base. I want to retrieve it in the readable date format. For that I use the following query select datetime(sdate,'unixepoch') from mytab; It shows a formatted date, but there is some changes in the hours. How can I retrieve

Re: [sqlite] Retrieving date

UCT vs. local time, perchance? Markus Lloyd [EMAIL PROTECTED] was heard to say: Hi, I stored a unix epoch (32 bit integer) date in the sqlite data base. I want to retrieve it in the readable date format. For that I use the following query select datetime(sdate,'unixepoch') from mytab;

Re: [sqlite] Q about new SQLite API

On 11/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: * You never get an SQLITE_SCHEMA error. sqlite3_prepare_v2 retains the original SQL and automatically reprepares and rebinds it following a schema change. Does this part of the change require a new API at all? * sqlite3_step()

RE: [sqlite] Re: Q about new SQLite API

-Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 07, 2006 8:15 AM To: SQLite Subject: [sqlite] Re: Q about new SQLite API Marco Bambini [EMAIL PROTECTED] wrote: 2. maybe with the new sqlite3_compile routine there should also be a way to

Re: [sqlite] Q about new SQLite API

Hello, Thanks for the improvements! Q1 Any name would be find, though the 'ex' extension seems to be popular for that sought of thing. Q2 A non-blocking resultset API? :) Sorry, had to try. Q3 I think this should be a new error for the caution's sake. But overall I prefer more, finer grain,

Re: [sqlite] String -- Integer date formatting

Dennis Cote wrote: Emmanuel, Check out the functions on this page http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions In particular the function julianday(datestring) will return a julian day number for a suitably formatted date string. The supported date formats are from ISO-8601,

Re: [sqlite] Q about new SQLite API

On Nov 7, 2006, at 5:17 AM, [EMAIL PROTECTED] wrote: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? What about introducing an options mechanism in place of a second prepare mechanism? Do you foresee any situations

Re: [sqlite] Date data type

IMO, dates are a pain. I spent considerable time trying to decide how best to store dates in my app(s), and eventually chose to use Unix times (integers). It seemed an easy choice as I program in Perl and JavaScript. Lately, I've begun to regret the choice I made. Every ad-hoc query I need

Re: [sqlite] Q about new SQLite API

Q1: sqlite3_prepare_ex Q3: SQLITE_SCHEMA I don't currently use the APIs directly (though I have a project in mind), but these seem to make the most sense. -Clark - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, November 7, 2006

Re: [sqlite] Question on Sqlite DB

Hi, can SQLite receive as input a file containing SQL instructions? Thank you Benedetta Turchi Engineering Tools Engineering Tools Website - *** *** Symbian Software Ltd is a company

Re: [sqlite] Retrieving date

Lloyd [EMAIL PROTECTED] writes: Hi, I stored a unix epoch (32 bit integer) date in the sqlite data base. I want to retrieve it in the readable date format. For that I use the following query select datetime(sdate,'unixepoch') from mytab; It shows a formatted date, but there is some

[sqlite] Re: Date data type

* Clark Christensen [EMAIL PROTECTED] [2006-11-07 18:05]: If I had it to do over, I would probably store my datetimes as -MM-DD HH:MM:SS strings. Make that a “T” instead of a space, and add timezone offset info (either “Z” for UTC or “+HHMM” for an offset), then you have RFC 3339 datetime

Re: [sqlite] Re: Q about new SQLite API

On 11/7/06, Igor Tandetnik [EMAIL PROTECTED] wrote: In general, there is no rowid associated with a row returned by select. A row may be constructed out of data taken from multiple rows of multiple tables, or be manufactured without reference to any table at all. If you want a rowid from a

Re: [sqlite] Question on Sqlite DB

[EMAIL PROTECTED] wrote: can SQLite receive as input a file containing SQL instructions? Benedetta, The SQLite library can't do that itself, but the command shell program that comes with it can using the .read command. sqlite3 mydb.db3 .read mysql.txt This will open (or create) the

Re: [sqlite] Q about new SQLite API

[EMAIL PROTECTED] wrote: * sqlite3_step() returns the correct error code right away, rather than just returning SQLITE_ERROR and making you call sqlite3_reset() to find the true reason for the error. I actually prefer the existing behaviour. If you do start returning the error

Re: [sqlite] Date data type

On Tue, 7 Nov 2006, Me wrote: -MM-DD HH:MM:SS behaves as expected on queries no manipulation necessary you can set a validation mask CREATE TABLE [tEnforceTimestamp2] ( [cTimestamp] TEXT CHECK (cTimestamp LIKE'-__-__ __:__:__') ) or CREATE TABLE [tEnforceTimestamp2] (

[sqlite] How sqlite works? Parsing SQL ect

Hi, I would like to build a very light SQL database in javascript. I would like to learn how sqlite parses an SQL statement and then acts on this parsed information. Does anyone have a pointer to a link or book that explains the process at a higher level then source code? Thank you, Peter

Re: [sqlite] How sqlite works? Parsing SQL ect

Peter Michaux wrote: Does anyone have a pointer to a link or book that explains the process at a higher level then source code? http://sqlite.org/arch.html Roger - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Q about new SQLite API

[EMAIL PROTECTED] wrote: I'm working on a new API routine for SQLite and I have questions for the community. The working name of the new api is sqlite3_prepare_v2(). sqlite3_prepare_v2() works like sqlite3_prepare() in that it generates a prepared statement in an sqlite3_stmt structure. The

Re: [sqlite] Q about new SQLite API

At 1:17 PM + 11/7/06, [EMAIL PROTECTED] wrote: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? I like sqlite3_prepare_v2. It lets me know that it's very similar to sqlite3_prepare, so lots of existing documentation

Re: [sqlite] Question on Sqlite DB

Yes [EMAIL PROTECTED] wrote: Hi, can SQLite receive as input a file containing SQL instructions? Thank you Benedetta Turchi Engineering Tools Engineering Tools Website - *** ***

Re: [sqlite] Loading a table into memory

On 11/7/06, chetana bhargav [EMAIL PROTECTED] wrote: I am trying for loading only that table one into memory. Beacuse in my DB I have two or more three table which are quite large and I don't want to waste memory. Is it possible that way. I believe you can attach the database :memory: to

[sqlite] Date and time

Hi, I felt sqlite's date and time functions are very useful and suits my needs. But still am not clear about one thing. I gave the following query select datetime(1162961284,'unixepoch'); 2006-11-08 04:48:04 The date is correct but the time is 5 hours lagging. So for local time correction I

Re: [sqlite] Date data type

Lloyd wrote: Thanks gg and Craig Morrison for your informative reply. I would like to know one more thing, can I use these date and time functions with comparison operators? Will they return the correct result or as per the string comparison rules? Same rules apply, if it smells like a

Re: [sqlite] Date and time

Lloyd wrote: select datetime(1162961284,'unixepoch','localtime'); 2006-11-08 10:18:04 The result is correct. I would like to know how sqlite is performing the localtime correction. timestamps are in relation to UTC.. When you use the localtime modifier, you are instructing the code to

Re: [sqlite] Date and time

Thanks Craig Morrison. Now I got the idea clearly. On Wed, 2006-11-08 at 00:55 -0500, Craig Morrison wrote: Lloyd wrote: select datetime(1162961284,'unixepoch','localtime'); 2006-11-08 10:18:04 The result is correct. I would like to know how sqlite is performing the localtime