Re: [sqlite] need help with a query using datetime

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> sqlite> create table t(d date);
> sqlite> insert into t values('2011-12-31 09:00');
> sqlite> insert into t values('2011-12-31 12:15');
> sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t;
> 2011-12-31 09:00|2011-12-30 21:00
> 2011-12-31 12:15|2011-12-31 00:15

In place of datetime and substr, consider

strftime('%Y-%m-%d %H:%M', d, '-12 hours')

-- 
Igor Tandetnik

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

Re: [sqlite] need help with a query using datetime

[Ruth Ivimey-Cook]

On 17/06/2011 12:10, looki wrote:
> First column holds the givin datetime from my table and the second column
> should show the datetime from first row but 12 hours before. for example:
>
> '2011-12-31 09:00' '2011-12-30 21:00'
> '2011-12-31 12:15' '2011-12-30 00:15'
> ...
>
> looks simple but datetime was not sufficient for this in my research.

For MySQL, the adddate() operator will do the trick:
  select theDate, adddate(theDate,interval -12 hour) from table;

Does Sqlite have this too?
Ruth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] need help with a query using datetime

[Black, Michael (IS)]

sqlite> create table t(d date);
sqlite> insert into t values('2011-12-31 09:00');
sqlite> insert into t values('2011-12-31 12:15');
sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t;
2011-12-31 09:00|2011-12-30 21:00
2011-12-31 12:15|2011-12-31 00:15



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of looki [looki1...@yahoo.com]
Sent: Friday, June 17, 2011 6:10 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] need help with a query using datetime


Hi,
i have a table which holds dates like
'2011-12-31 09:00' and i now want to write a query which gives me 2 columns.

First column holds the givin datetime from my table and the second column
should show the datetime from first row but 12 hours before. for example:

'2011-12-31 09:00' '2011-12-30 21:00'
'2011-12-31 12:15' '2011-12-30 00:15'
...

looks simple but datetime was not sufficient for this in my research.

Very thanks for your help.

Greetz looki



--
View this message in context: 
http://old.nabble.com/need-help-with-a-query-using-datetime-tp31868064p31868064.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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