Re: [sqlite] joint two table

[Igor Tandetnik]

On 2/1/2012 4:13 PM, Pawl wrote:

Can I eliminate this problem by using function strftime('%s',timestampevar)


Your strings are not in any format that strftime accepts. strftime('%s', 
'15.01.2011') returns NULL.


You really, really should change the format of your timestamps. You 
could do it in bulk, by running a statement like this:


update JX set edittime=substr(edittime, 7) || '-' || substr(edittime, 4, 
2) || '-' || substr(edittime, 1, 2);


(a matching statement for JX_lim is left as an exercise for the reader). 
Of course, you'd then need to update your software to read and write the 
new format, going forward.

--
Igor Tandetnik

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

Re: [sqlite] joint two table

[Pawl]

Can I eliminate this problem by using function strftime('%s',timestampevar)

it try to rewrute:

select * from JX, JX_lim lim1 where lim1.rowid = (
select rowid from JX_lim lim2
where strftime('%s',lim2.edittime) <= strftime('%s',JX.starttime)
order by lim2.edittime desc limit 1 

it is correct? 
-- 
View this message in context: 
http://old.nabble.com/joint-two-table-tp33228376p33245195.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

Re: [sqlite] joint two table

[Igor Tandetnik]

Pawl  wrote:
> I don't know  that sqllite save TIMESTEMP as string, problem is with
> counting i have to use helpful function
> like as strftime('%s',timestampevar) .. but ... comparative operator <> =
> BETWEEN is right.

It only seems this way, because all dates in your sample database happen to 
come the same month and year. Once you work with real dates, you'll discover 
that '20.02.2012' < '30.01.2010', for example.
-- 
Igor Tandetnik

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

Re: [sqlite] joint two table

[Pawl]

Thanks it work. 

I don't know  that sqllite save TIMESTEMP as string, problem is with
counting i have to use helpful function 
like as strftime('%s',timestampevar) .. but ... comparative operator <> =
BETWEEN is right. 

Thanks 



Igor Tandetnik wrote:
> 
> ...
> You probably want something like
> 
> select * from JX, JX_lim lim1 where lim1.rowid = (
> select rowid from JX_lim lim2
> where lim2.edittime <= JX.starttime
> order by lim2.edittime desc limit 1
> );
> 
> But first you'd need to address the issue with data format that Simon
> pointed out. The way you have it, comparisons won't work right.
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/joint-two-table-tp33228376p33241938.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

Re: [sqlite] joint two table

[Igor Tandetnik]

Pawl  wrote:
> I use TIMESTAMP datatype, disply format is according to local cuture setting
> ex: 27.1.2012 13:50:32.
> all compare type is same.

There is no TIMESTAMP datatype in SQLite. Your dates are stored as strings, and 
are being compared using regular alphabetical comparison. For details, see 
http://sqlite.org/datatype3.html

It is advisable to store dates, times or timestamps in one of the formats 
described at http://sqlite.org/lang_datefunc.html . First, built-in date/time 
functions can work with them, if you ever find yourself in need of doing 
calculations on those timestamps. Second, and of immediate importance, these 
formats are designed in such a way that plain vanilla string or numeric 
comparison just happens to order dates and times correctly.

So, store dates in the database in one of these culture-neutral format. Convert 
them to and from culture-appropriate format in your application code, when 
formatting the data for presentation or accepting user input.
-- 
Igor Tandetnik

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

Re: [sqlite] joint two table

[Igor Tandetnik]

Pawl  wrote:
> 
> I need to joint two table
> 
> This table show change set up limts value.
> 
> ++---+---+
>>   edittime | lim_lower | lim_upper |
> ++---+---+
>> 01.01.2011 | 0.8   | 12.1  |
> ++---+---+
>> 05.01.2011 | 0.7   | 12.1  |
> ++---+---+
>> 11.01.2011 | 0.1   | 11.8  |
> ++---+---+
> 
> 
> This is measure value.
> ++---+---+
>> startttime | value | operatorid|
> ++---+---+
>> 01.01.2011 | 7.7   | 1 |
> ++---+---+
>> 02.01.2011 | 17.1  | 1 |
> ++---+---+
>> 03.01.2011 | 6.2   | 1 |
> ++---+---+
>> 04.01.2011 | 7.7   | 1 |
> ++---+---+
>> 05.01.2011 | 17.1  | 1 |
> ++---+---+
>> 06.01.2011 | 6.2   | 1 |
> ++---+---+
>> 09.01.2011 | 7.7   | 1 |
> ++---+---+
>> 10.01.2011 | 12.1  | 1 |
> ++---+---+
>> 15.01.2011 | 6.7   | 1 |
> ++---+---+
> 
> I need make summary table like this:
> 
> ++---+---+--+---+
>> startttime | value |  edittime |lim_lower | lim_upper |
> ++---+---+--+---+
>> 01.01.2011 | 7.7   | 01.01.2011| 0.8  | 12.1  |
> ++---+---+--+---+
>> 02.01.2011 | 17.1  | 01.01.2011| 0.8  | 12.1  |
> ++---+---+--+---+
>> 03.01.2011 | 6.2   | 01.01.2011| 0.8  | 12.1  |
> ++---+---+--+---+
>> 04.01.2011 | 7.7   | 01.01.2011| 0.8  | 12.1  |
> ++---+---+--+---+
>> 05.01.2011 | 7.7   | 01.05.2011| 0.7  | 12.1  |
> ++---+---+--+---+
> ...
> 
> I tried to use cmd JOIN in this content, but didn't work it.
> 
> SELECT JX.*,JX_lim.* FROM JX JOIN JX_lim ON JX_lim.edittime <= JX.starttime

You probably want something like

select * from JX, JX_lim lim1 where lim1.rowid = (
select rowid from JX_lim lim2
where lim2.edittime <= JX.starttime
order by lim2.edittime desc limit 1
);

But first you'd need to address the issue with data format that Simon pointed 
out. The way you have it, comparisons won't work right.
-- 
Igor Tandetnik

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

Re: [sqlite] joint two table

[Pawl]

I use TIMESTAMP datatype, disply format is according to local cuture setting
ex: 27.1.2012 13:50:32.
all compare type is same. 

Pavel


Simon Slavin-3 wrote:
> 
> 
> On 30 Jan 2012, at 11:16am, Pawl wrote:
> 
>> | 05.01.2011 | 7.7   | 01.05.2011| 0.7  | 12.1  |
>> ++---+---+--+---+
>> ...
>> 
>> I tried to use cmd JOIN in this content, but didn't work it. 
>> 
>> SELECT JX.*,JX_lim.* FROM JX JOIN JX_lim ON JX_lim.edittime <=
>> JX.starttime
> 
> Your dates are stored in with the components in an unuseful order.  SQLite
> doesn't have a datetime format.  Values like the ones above will probably
> be stored as strings.  If you want your SELECT to work (and I don't think
> there's anything wrong with it) then your dates should be stored in a
> format like
> 
> "20110105" (string)
> "2011/01/05" (string)
> "2011-01-05" (string)
> unix epoch (floating point)
> juliandate (integer)
> 
> You have to store all dates in the same format, so pick one of the above
> (or something similar) and be consistent.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/joint-two-table-tp33228376p33228881.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

Re: [sqlite] joint two table

[Simon Slavin]

On 30 Jan 2012, at 11:16am, Pawl wrote:

> | 05.01.2011 | 7.7   | 01.05.2011| 0.7  | 12.1  |
> ++---+---+--+---+
> ...
> 
> I tried to use cmd JOIN in this content, but didn't work it. 
> 
> SELECT JX.*,JX_lim.* FROM JX JOIN JX_lim ON JX_lim.edittime <= JX.starttime

Your dates are stored in with the components in an unuseful order.  SQLite 
doesn't have a datetime format.  Values like the ones above will probably be 
stored as strings.  If you want your SELECT to work (and I don't think there's 
anything wrong with it) then your dates should be stored in a format like

"20110105" (string)
"2011/01/05" (string)
"2011-01-05" (string)
unix epoch (floating point)
juliandate (integer)

You have to store all dates in the same format, so pick one of the above (or 
something similar) and be consistent.

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

[sqlite] joint two table

[Pawl]

Hi, 

I need to joint two table 

This table show change set up limts value.

++---+---+
|   edittime | lim_lower | lim_upper |  
++---+---+
| 01.01.2011 | 0.8   | 12.1  |
++---+---+
| 05.01.2011 | 0.7   | 12.1  |
++---+---+
| 11.01.2011 | 0.1   | 11.8  |
++---+---+


This is measure value. 
++---+---+
| startttime | value | operatorid|  
++---+---+
| 01.01.2011 | 7.7   | 1 |
++---+---+
| 02.01.2011 | 17.1  | 1 |
++---+---+
| 03.01.2011 | 6.2   | 1 |
++---+---+
| 04.01.2011 | 7.7   | 1 |
++---+---+
| 05.01.2011 | 17.1  | 1 |
++---+---+
| 06.01.2011 | 6.2   | 1 |
++---+---+
| 09.01.2011 | 7.7   | 1 |
++---+---+
| 10.01.2011 | 12.1  | 1 |
++---+---+
| 15.01.2011 | 6.7   | 1 |
++---+---+

I need make summary table like this: 

++---+---+--+---+
| startttime | value |  edittime |lim_lower | lim_upper | 
++---+---+--+---+
| 01.01.2011 | 7.7   | 01.01.2011| 0.8  | 12.1  |
++---+---+--+---+
| 02.01.2011 | 17.1  | 01.01.2011| 0.8  | 12.1  |
++---+---+--+---+
| 03.01.2011 | 6.2   | 01.01.2011| 0.8  | 12.1  |
++---+---+--+---+
| 04.01.2011 | 7.7   | 01.01.2011| 0.8  | 12.1  |
++---+---+--+---+
| 05.01.2011 | 7.7   | 01.05.2011| 0.7  | 12.1  |
++---+---+--+---+
...

I tried to use cmd JOIN in this content, but didn't work it. 

SELECT JX.*,JX_lim.* FROM JX JOIN JX_lim ON JX_lim.edittime <= JX.starttime

Do you have any idea?

Thanks

Pavel samek


-- 
View this message in context: 
http://old.nabble.com/joint-two-table-tp33228376p33228376.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