Re: [sqlite] select where date < 30 days ago

[stefanos sofroniou]

Miklos, have you tried something like 


SELECT * FROM ticket
WHERE time 
BETWEEN '2011-08-01' AND '2011-09-01';

?

I have my "time" as current_timestamp and gives me data like "2011-09-23 
17:44:48", so using the above method works just fine with me.




>
>From: Miklos Koren 
>To: sqlite-users@sqlite.org
>Sent: Friday, September 16, 2011 3:01 PM
>Subject: [sqlite] select where date < 30 days ago
>
>Hi,
>
>This is probably a basic SQL question, but I could not make it work under
>sqlite. How do I select records for which a datetime column is less than
>today - 30 days? I tried
>
>SELECT * FROM ticket WHERE time < DATETIME('now','-30 days');
>
>but it does not give the intended results.
>
>Thanks,
>
>Miklos
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where date < 30 days ago

[Igor Tandetnik]

Miklos Koren  wrote:
> I was finally able to make it work with
> 
> WHERE CAST(time AS int)/100/86400+2440588 < JULIANDAY('now','-30days')

It appears your timestamp is in microseconds since 1/1/1970. In this case, it 
might be a bit more straightforward to write

where time < strftime('%s', 'now', '-30 days') * 100
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where date < 30 days ago

[Miklos Koren]

I was finally able to make it work with

WHERE CAST(time AS int)/100/86400+2440588 < JULIANDAY('now','-30days')

2011/9/16 Igor Tandetnik 

>
> Show your data, show the results you get from the statement, and explain
> how the observed outcome differs from your expectations.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where date < 30 days ago

[Igor Tandetnik]

Miklos Koren  wrote:
> This is probably a basic SQL question, but I could not make it work under
> sqlite. How do I select records for which a datetime column is less than
> today - 30 days? I tried
> 
> SELECT * FROM ticket WHERE time < DATETIME('now','-30 days');
> 
> but it does not give the intended results.

Show your data, show the results you get from the statement, and explain how 
the observed outcome differs from your expectations.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users