85338

How to structure my query in sqlite?

Question:

I would like to retrieve today's data. At the moment I have something like SELECT * FROM myTable WHERE timeStamp>DATETIME('NOW','-1 DAY') but this gives me results from now to 24hrs back, not just today (i.e. no matter how many hours have passed since 00:00). Using the same logic I want to retrieve data for just yesterday, and for the this week.

<strong>[EDIT]</strong> By the term this week i mean.. if today is Thursday, i want to show results from monday or Sunday (it doesnt matter) upto now.

Answer1:

Instead of timeStamp > DATETIME('now') use timeStamp >= DATE('now').

For since yesterday you can use DATE('now', '-1 day').

As for this week - it depends if you mean 7 days ago:

DATE('now', '-7 days')

Or if you mean since the beginning of the first day of this week:

DATE('now', 'weekday 0', '-7 days')

Recommend

  • How to use correct regional format (date & time) of Windows Phone 8.1
  • Re-training inception google cloud stuck at global step 0
  • How to use data from Mongo and PostgreSQL as in-memory lookup tables?
  • Additional scripts in PATH
  • Cannot convert a char value to money. The char value has incorrect syntax
  • Is there a chance to get -splash: work for SWT applications that require -XstartOnFirstThread?
  • MySql - get days remaining
  • Returning this from a constructor function in JS
  • Java : How to tint this PNG programmatically?
  • include dlls in visual studio c++ 2008
  • PHP multiple file uploads
  • What is the use of a session store?
  • How to get the date of next specified day of week
  • HttpListener.IsSupported is false on XP SP3
  • Android cannot disable cut copy paste
  • R Split data.frame using a column that represents and on/off switch
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • Low TTL with Leveled Compaction, should I reduce gc_grace_seconds to improve read performance withou
  • RxJava debounce by arbitrary value
  • How to assign byte[] as a pointer in C#
  • Is playing sound in Javascript performance heavy?
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • jQuery ready not fired after rails link_to is clicked
  • onBackPressed() not being executed
  • Do I need to reset a Perl hash index?
  • ActiveRecord query for a count of new users by day
  • Insert into database using onclick function
  • Illegal mix of collations for operation for date/time comparison
  • How would I use PHP exceptions to define a redirect?
  • javascript inside java/jsp code
  • Knitr HTML Loop - Some HTML output, some R output
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • ActionScript 2 vs ActionScript 3 performance
  • Why joiner is not used after Sequence generator or Update statergy
  • embed rChart in Markdown
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • How can I use threading to 'tick' a timer to be accessed by other threads?