LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-06-2004, 02:07 PM   #1
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Rep: Reputation: 15
sql timestamp help


good day to all.

i want to achieve list of rows for Average of encoder speed input.
i have this table name cocv_2004 and with the ff columns:
-- cocv_id
-- personality_id
-- province_code
-- municipality_code
-- votes
-- db_user
and
-- timestamp

can anyone help me how to select out from this table of an avegare speed refering from timestamp column? is there a way to compute time in sql queries function?
 
Old 10-06-2004, 02:23 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
How could you possibly compute speed if you only
have one timestamp value? I don't understand... you'd
need to have two timestamps, one for start, one for
end, and then you could calculate the average over
the differences.


Cheers,
Tink
 
Old 10-06-2004, 09:53 PM   #3
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
Quote:
Originally posted by Tinkster
How could you possibly compute speed if you only
have one timestamp value? I don't understand... you'd
need to have two timestamps, one for start, one for
end, and then you could calculate the average over
the differences.


Cheers,
Tink
Hi thanks for the reply, Sorry im doop!

anyways here's my sencond table name cc_encodersee with columns:
-- cc_encodersee_id
-- cc_id
-- polparty_id
-- db_user
and
-- timestamp
...........................
now the two tables are here with both have time stamp? can u direct me how to make s calculation of average speed?
 
Old 10-08-2004, 06:37 PM   #4
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Not tested, but something along the lines of:

SELECT avg( a.timestamp - b.timestamp)
FROM cocv_2004 AS a
JOIN cc_encodersee AS b ON a.db_user = b.db_user

That's the only possible criteria I can think of :)


Cheers,
Tink
 
Old 10-11-2004, 12:17 AM   #5
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
Quote:
Originally posted by Tinkster
Not tested, but something along the lines of:

SELECT avg( a.timestamp - b.timestamp)
FROM cocv_2004 AS a
JOIN cc_encodersee AS b ON a.db_user = b.db_user

That's the only possible criteria I can think of


Cheers,
Tink
THANKS again for your reply.

i need your help again.
letsay i have this content on my table cc_encodersee:

--id--|----cc_id-------|----db_user----|--------- timestamp
------|-----------------|------------------|---------------------------------------
--3---|----91000-----|----gdilao-------|-- 2004-02-06 17:52:30.072632
--4---|----91000-----|----gdilao-------|-- 2004-20-06 19:00:08.420871
--5---|----91000-----|----gdilao-------|-- 2004-02-09 11:18:20.384175
--6---|----91000-----|----gdilao-------|-- 2004-02-09 11:31:54.352862
--7---|----91000-----|----gdilao-------|-- 2004-02-09 11:55:31.334232
--8---|----91000-----|----gdilao-------|-- 2004-02-09 12:07:32.137286
:
:
--25---|----91000-----|----gdilao-------|-- 2004-02-09 19:26:08.883724

can i select with the timestamp with a specific date given from a query to compute for an average
from table row 5 upto row 25 was 2004/02/09 of date?

at the back of my head i have a query like this but i know its not right.:
SELECT AVG(timestamp) FROM cc_encodersee WHERE timestamp = '2004-02-09' and db_user = 'gdilao';
just giving you an idea what i want to achieve. thanks again.

Last edited by vickr1z; 10-11-2004 at 01:07 AM.
 
Old 10-12-2004, 05:40 PM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Quote:
Originally posted by vickr1z
THANKS again for your reply.

i need your help again.
letsay i have this content on my table cc_encodersee:

--id--|----cc_id-------|----db_user----|--------- timestamp
------|-----------------|------------------|---------------------------------------
--3---|----91000-----|----gdilao-------|-- 2004-02-06 17:52:30.072632
--4---|----91000-----|----gdilao-------|-- 2004-20-06 19:00:08.420871
--5---|----91000-----|----gdilao-------|-- 2004-02-09 11:18:20.384175
--6---|----91000-----|----gdilao-------|-- 2004-02-09 11:31:54.352862
--7---|----91000-----|----gdilao-------|-- 2004-02-09 11:55:31.334232
--8---|----91000-----|----gdilao-------|-- 2004-02-09 12:07:32.137286
:
:
--25---|----91000-----|----gdilao-------|-- 2004-02-09 19:26:08.883724

can i select with the timestamp with a specific date given from a query to compute for an average
from table row 5 upto row 25 was 2004/02/09 of date?

at the back of my head i have a query like this but i know its not right.:
SELECT AVG(timestamp) FROM cc_encodersee WHERE timestamp = '2004-02-09' and db_user = 'gdilao';
just giving you an idea what i want to achieve. thanks again.
Your select is close enough ... does this work?

Code:
SELECT AVG(timestamp) FROM cc_encodersee WHERE to_char( timestamp,'YYYY-MM-DD')  LIKE '2004-02-09%' and db_user = 'gdilao' AND (id BETWEEN 5 AND 25);

Cheers,
Tink

P.S.: I hope that you don't do this all for a living ;)
 
Old 10-12-2004, 09:24 PM   #7
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
Quote:
Originally posted by Tinkster

Code:
SELECT AVG(timestamp) FROM cc_encodersee WHERE to_char( timestamp,'YYYY-MM-DD')  LIKE '2004-02-09%' and db_user = 'gdilao' AND (id BETWEEN 5 AND 25);
Hi i used the query you post but i got this weird error:
QUERY:
SELECT AVG(timestamp) FROM cc_encodersee WHERE to_char( timestamp,'YYYY-MM-DD') LIKE '2004-02-09%' and db_user = 'gdilao' AND (id BETWEEN 5 AND 25);
ERROR:
FUNCTION avg(timestamps) does not exist
Unable to identify the functions that satisfy the given argument types
You may need to add explicit typecast.

for sure its not an typo error issue on my query coz the column 'timestamp' was exist on my table but on error function it was reffering to 'avg(timestamps)' what this mean?

pls help again,
kriz

PS: im really sorry to bother you but i really get hard to read SQL tutorial right now. my boss just leave me on her desk to work this things out.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
timestamp in the future comprookie2000 Linux - General 5 05-19-2011 06:45 AM
Timestamp Help jch02140 Linux - Newbie 7 03-17-2006 03:33 AM
Migrating from MS-SQL server to My-SQL emailssent Linux - Networking 2 02-07-2005 03:20 PM
Which SQL is suitable for EMbedded SQL on C? hk_michael Programming 4 01-10-2005 06:07 PM
adding timestamp to name niehls Linux - General 2 12-05-2003 11:40 AM


All times are GMT -5. The time now is 06:43 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration