Re: [sqlite] dates, times and R

Thanks for the idea but the interface already handles that and does it without special names. The last example in my last post shows that d was correctly typed in the output because the interface noticed that it had the same name as an input column. Other problems are that it would still not

Re: [sqlite] dates, times and R

Monday, August 12, 2019, 1:06:00 PM, Gabor Grothendieck wrote: > The whole point of this is to make it as easy as possible for the user. > With other backends the database handles the types but with sqlite > the user has to get involved. > ... > sqldf("select d as d__Date, d + 1 as

Re: [sqlite] dates, times and R

The whole point of this is to make it as easy as possible for the user. With other backends the database handles the types but with sqlite the user has to get involved. It is not a matter of storage. It is a matter of maintaining the type information on the database side and passing the type

Re: [sqlite] dates, times and R

On Sunday, 11 August, 2019 07:45, Gabor Grothendieck wrote: >R supports Date and POSIXct (date/time) classes which are represented >internally as days and seconds since the UNIX Epoch respectively; >however, due to the class it knows to display and manipulate them as >dates and datetimes

Re: [sqlite] dates, times and R

Actually sqldf has multiple heuristics and the one you suggested is already one of them (except for minor differences in syntax) but this has the disadvantage that the user must specify classes whereas if the user simply uses any of the other backends they don't have to. On Sun, Aug 11, 2019 at

Re: [sqlite] dates, times and R

On 11 Aug 2019, at 2:45pm, Gabor Grothendieck wrote: > R supports Date and POSIXct (date/time) classes which are represented > internally as days and seconds since the UNIX Epoch respectively; > however, due to the class it knows to display and manipulate them as > dates and datetimes rather

[sqlite] dates, times and R

It's really useful that SQLite now supports window operations as that was one of the key features that R users needed to do many manipulations using SQLite. From the perspective of the R language there is really one particularly key feature left that prevents some users from easily using SQLite

Re: [sqlite] dates and optimizations

On 12/7/2012 11:35 AM, Simon Slavin wrote: most of my systems store dates as strings in this format "MMDD". Better still (IMHO), integers in the format MMDD (e.g. 20121207). Integers are stored a bit more compactly by SQLite, and are compared slightly faster. And you can still

Re: [sqlite] dates and optimizations

> > > But do you really do things like run a search on "invoices made ​​on a Monday > in February" ? Yes. For example, in a store, it is useful to have sales statistics by day of the week, or by time slot, etc.. > Most of the time I store a date I'm storing it for three purposes: > > A)

Re: [sqlite] dates and optimizations

On 7 Dec 2012, at 4:24pm, Paxdo Presse wrote: > select invoice.date from invoice where strftime('%m', invoice.date)='02' and > strftime('%w', invoice.date)='1' > > For invoices made ​​on a Monday in February. > From your experience, these requests are they fast? 'fast' is the

[sqlite] dates and optimizations

Hello, To search and sort the dates in SQLite, the SQL methods (strftime and date) are they fast enough on long dates (as '2012-12-07 01:48:45')? Or would you prefer to store separately the day, year, etc..? example: select invoice.date from invoice where strftime('%m', invoice.date)='02'

Re: [sqlite] Dates based on fliter

My understanding on the below code is that this is adding one month to the current date. I need to add months to the date listed in a table. Simon Slavin-3 wrote: > > > On 28 Oct 2010, at 7:58pm, Redhot wrote: > >> the first filter would be the Date entered to >> Six months from the Date

Re: [sqlite] Dates based on fliter

...@sqlite.org on behalf of Redhot Sent: Thu 10/28/2010 1:58 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] Dates based on fliter I would like to add the 6 months, 12 months 18 months and 24 months to a date entered into the sqlite database. The date in the database is in sql form

Re: [sqlite] Dates based on fliter

On 28 Oct 2010, at 7:58pm, Redhot wrote: > the first filter would be the Date entered to > Six months from the Date Entered. The Next would be Six months +1 to twelve > months from the date entered. See http://www.sqlite.org/lang_datefunc.html and the example that says SELECT

[sqlite] Dates based on fliter

I would like to add the 6 months, 12 months 18 months and 24 months to a date entered into the sqlite database. The date in the database is in sql formate -MM-DD. The dates will be used for a filter. The filter will pull Rows from a table. Like the first filter would be the Date entered to

Re: [sqlite] dates

Quoting ga...@schoolteachers.co.uk: > I am trying to get date testing and manipulation to work. Should this work: > > select julianday('now') - julianday(startmonday) from wb > > startmonday is a text field that contains 2009-03-30. > > Also the following returns 'none' > > select

[sqlite] dates

I am trying to get date testing and manipulation to work. Should this work: select julianday('now') - julianday(startmonday) from wb startmonday is a text field that contains 2009-03-30. Also the following returns 'none' select julianday(startmonday) from wb Any help would be much

Re: [sqlite] Dates & SQLite

I use ISO time, which is 20080916122801 as I write this. It can be stored in integer format, is very easy to manipulate and sort. YearMonthDateHourMinuteSecond Brown, Daniel wrote: > > Good morning list, > > Could someone point me to the documentation regarding dates and SQLite? > I'm having

Re: [sqlite] Dates & SQLite

(integers), but those are slightly more cumbersome, because they require an aditional keyword ('unixepoch') to process. g -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2008 11:23 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dates &

Re: [sqlite] Dates & SQLite

Brown, Daniel <[EMAIL PROTECTED]> wrote: > Could someone point me to the documentation regarding dates and > SQLite? http://sqlite.org/lang_datefunc.html > I'm having trouble finding anything about what data type I > should use to store dates in my SQLite tables, should it be a > numerical type

[sqlite] Dates & SQLite

Good morning list, Could someone point me to the documentation regarding dates and SQLite? I'm having trouble finding anything about what data type I should use to store dates in my SQLite tables, should it be a numerical type (integer or real) or a string? Cheers, Daniel Brown | Software

Re: [sqlite] dates and times in sqlite

First make sure that you store the date in Julian format by using the julianday function. Then you should be able to get the day of the week by take modulo 7 of the julian date. You can use the strftime function to do that - **strftime( FORMAT, TIMESTRING, MOD, MOD, ...) ** ** Return a

[sqlite] dates and times in sqlite

Hi, I've made a text field called "timestamp" which has dates in the form: -MM-DD HH:MM:SS I want to test if the day portion is a Tuesday for example - something like: SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY is something like that at all possible? I've looked at the

[sqlite] Dates in sqlite3 databases and GUI tools

Hello All, From: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions "The julian day number is the preferred internal representation of dates." Unfortunately floating point values are not very readable when used with existing GUI tools. The only problem is visualization, GUI tools need to