Home » SQL & PL/SQL » SQL & PL/SQL » Date and Time comparison query - HELP (2 threads merged by bb)
Date and Time comparison query - HELP (2 threads merged by bb) [message #391068] Tue, 10 March 2009 19:37 Go to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
I creating an online cinema booking system and i need the system to display all the film showings after the current date and time, so no film showings in the past are displayed.

my table has a date and time columns.

the query i wrote 1st was;

select * from filmshowings where showdate > SYSDATE and showtime > to_char(SYSDATE, 'HH24:MI:SS');

however, although the above query works fine for the date, the time part is not valid. For example, the query does not display film showings after the current TIME regardless of the date. So if i run the query at 21.00, it will not display film showings before 21.00 even if the showings next week.

How can i write a query which displays all film showings after the current date in 1 single query?

thanks!
Re: Oracle Date and Time Comparison query - HELP [message #391070 is a reply to message #391068] Tue, 10 March 2009 19:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.


http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions180.htm#sthref2295
Re: Date and Time comparison query - HELP! [message #391071 is a reply to message #391068] Tue, 10 March 2009 19:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multi-post
Re: Oracle Date and Time Comparison query - HELP [message #391072 is a reply to message #391068] Tue, 10 March 2009 19:48 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member

showdate date
showtime varchar2(20)

e.g.
showdate: 21-MAR-09
showtime: 21:00:00

how can i view a filmshowing for that date if i run the query after 21:00:00?

i hope i've made it more clear.




Re: Oracle Date and Time Comparison query - HELP [message #391073 is a reply to message #391068] Tue, 10 March 2009 19:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref118

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions180.htm#sthref2295

DATE datatype includes time & you should make use of this capability
Re: Oracle Date and Time Comparison query - HELP [message #391074 is a reply to message #391068] Tue, 10 March 2009 20:06 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
if i'd created the column showtime which included both the date and time then the query would have been no problem, however assuming this can't be changed whats the solution?

or whats the logic behind it and i'll try to work it out my self.

i've looked at the 2 links you have provided, but it doesn't seem to be the solution.
Re: Oracle Date and Time Comparison query - HELP [message #391075 is a reply to message #391068] Tue, 10 March 2009 20:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>if i'd created the column showtime which included both the date and time then the query would have been no problem,
But showdate can hold both the date & time.

Since I have to way to reproduce your table and data, I have no solution for this.
Re: Oracle Date and Time Comparison query - HELP [message #391076 is a reply to message #391068] Tue, 10 March 2009 20:27 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
create table filmShowing
(
fsid number(7) not null,
filmid number(7) not null,
price number(2,2) not null,
showdate date not null,
showtime varchar2(20) not null,
seats_avbl number(3) not null,
constraint pk_fs_id primary key (fsid),
constraint fk_film_id foreign key (filmid) references film (filmid),
);


this is the table in question.

my query reads

select * from filmshowing where showdate >= SYSDATE and showtime > to_char(SYSDATE, 'HH24:MI:SS').


the above query only returns the film showings which are after the current time because of the "showtime > to_char(SYSDATE, 'HH24:MI:SS')."

what query will return all the film showings with valid date / time, so that a film showing next week is also displayed even though the showing time is after the sysdate.
Re: Oracle Date and Time Comparison query - HELP [message #391079 is a reply to message #391068] Tue, 10 March 2009 20:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
add another condition to the WHERE clause.

By the way, storing TIME in a VARCHAR2 is a really bad idea.

[Updated on: Tue, 10 March 2009 20:45]

Report message to a moderator

Re: Oracle Date and Time Comparison query - HELP [message #391080 is a reply to message #391068] Tue, 10 March 2009 20:48 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
add another condition to the WHERE clause.


such as...?
Re: Oracle Date and Time Comparison query - HELP [message #391081 is a reply to message #391068] Tue, 10 March 2009 20:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
a condition that works around the flawed design which is at the center of your current heartburn.

In both the short run & long run you would be MUCH better off changing the design & using showdate to hold both the date & time for the current showing.
Re: Oracle Date and Time Comparison query - HELP [message #391082 is a reply to message #391068] Tue, 10 March 2009 20:58 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
yes i understand that, but assuming this isn't possible, how can i write a query to do what i need to do.

i can change the showtime data type to DATE, but not really possible to drop the column.

so for now the design, in terms of 2 separate columns will remain for the near future atleast. i can change the data type if it makes it easier.

even then, can you suggest a query to achive my goal?
Re: Oracle Date and Time Comparison query - HELP [message #391083 is a reply to message #391068] Tue, 10 March 2009 21:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I have never made any comment or input on the showtime column.
I do not understand why you are fixated on this problematic column.
showtime is part of the problem & contributes nothing to any solution.


Re: Oracle Date and Time Comparison query - HELP [message #391095 is a reply to message #391082] Wed, 11 March 2009 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Convert the time varchar into seconds and compare with the second part of sysdate.
SQL> select sysdate, to_number(to_char(sysdate,'SSSSS')) seconds from dual;
SYSDATE                SECONDS
------------------- ----------
11/03/2009 06:20:16      22816

1 row selected.

SQL> select to_number(to_char(to_date('06:20:16','HH24:MI:SS'),'SSSSS')) from dual;
TO_NUMBER(TO_CHAR(TO_DATE('06:20:16','HH24:MI:SS'),'SSSSS'))
------------------------------------------------------------
                                                       22816

1 row selected.

Regards
Michel
Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391277 is a reply to message #391068] Wed, 11 March 2009 11:07 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
You have two different cases here.
1. showdate = trunc(sysdate) => You will have to also check showtime.
2. showdate > trunc(sysdate) => Ignore showtime. Any time of the day would be later than sysdate

If using your query:
select * 
from filmshowings 
where showdate > SYSDATE 
   or (trunc(showdate) = trunc(sysdate) 
       and showtime > to_char(SYSDATE, 'HH24:MI:SS'));

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391279 is a reply to message #391277] Wed, 11 March 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
showtime > to_char(SYSDATE, 'HH24:MI:SS'));

Wrong! '8:00' is greater than '21:00'

Regards
Michel
Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391281 is a reply to message #391068] Wed, 11 March 2009 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
c_stenersen,
Posting Guidelines advise against feeding noobies complete solutions.

A solution exists without use of OR.
Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391294 is a reply to message #391068] Wed, 11 March 2009 11:55 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
hi,

no worries, i have solved the problem with the following query

select * from filmshowing where showdate > to_char(SYSDATE, 'DD-MON-YY') or showdate = to_char(SYSDATE, 'DD-MON-YY') and showtime > to_char(SYSDATE, 'HH24:MI:SS');


it gets the desired result!!!


thanks

[Updated on: Wed, 11 March 2009 12:00]

Report message to a moderator

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391295 is a reply to message #391294] Wed, 11 March 2009 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it gets the desired result!!!

You were lucky.

And you still didn't follow the posting guidelines.

Regards
Michel
Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391299 is a reply to message #391294] Wed, 11 March 2009 12:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't worry about the posts telling you to post DDL/DML, or insisting you read the Forum Guide, or just being unhelpful - there's a depressing trend round here these days towards criticising questions rather than answering them.
It'd be nice if you did read the Sticky post at the top of the forum, but rest assured that some of us will try to answer your questions anyway.

There is a flaw in your query:
This line here
showtime > to_char(SYSDATE, 'HH24:MI:SS')
is doing a string comparison between the two variables rather than a numeric one.
This will work, if your strings always start with the hours in two characters, with a leading 0.
If you don't have a leading 0, and you had (say) a matinee performance at 9:00, then you'd end up comparing the strings 9:00:00 with (say) 20:01:54, and the rule of string comparisons say that the string with the highest leading digit is the higher string.

You would be better off comparing date like this:
to_date(showtime,'HH24:MI:SS') > to_date(to_char(sysdate,'hh24:mi:ss'),'hh24:mi:ss')

(If no day/month/year is specified for to_date, then it uses the first day of the current month)
Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391305 is a reply to message #391299] Wed, 11 March 2009 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I posted is not helpful and I'm just criticizing?
What about
Michel Cadot wrote on Wed, 11 March 2009 06:21
Convert the time varchar into seconds and compare with the second part of sysdate.
SQL> select sysdate, to_number(to_char(sysdate,'SSSSS')) seconds from dual;
SYSDATE                SECONDS
------------------- ----------
11/03/2009 06:20:16      22816

1 row selected.

SQL> select to_number(to_char(to_date('06:20:16','HH24:MI:SS'),'SSSSS')) from dual;
TO_NUMBER(TO_CHAR(TO_DATE('06:20:16','HH24:MI:SS'),'SSSSS'))
------------------------------------------------------------
                                                       22816

1 row selected.

Regards
Michel

Which gives a correct way to compare time.
Or
Michel Cadot wrote on Wed, 11 March 2009 17:16
Quote:
showtime > to_char(SYSDATE, 'HH24:MI:SS'));

Wrong! '8:00' is greater than '21:00'

Regards
Michel

Which just says the same thing as you but let the poster reflects about it and find the answer by himself.
In the end, what does your post add to mines, except more words and the (bad) use of implicit to_date behaviour?

Regards
Michel


[Updated on: Wed, 11 March 2009 13:25]

Report message to a moderator

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391333 is a reply to message #391068] Wed, 11 March 2009 17:51 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
the guidelines are;

Be polite! - I was


Never belittle anyone for asking beginner-level questions or for their English skills. - I did not

Use English language in the main forum (non-English forums are also provided - see bottom of list) and DO NOT use IM-speak! - I used good enough English

Provide all relevant information about your problem, including Oracle software version (4 decimal places) and operating system version. - I said Oracle 10g

Format your code and make sure that lines do not exceed 80 characters. Use the "Preview Message" button to check it. - I think i was OK here to.


So what part of te "guidelines" didn't i adhere to?

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391335 is a reply to message #391068] Wed, 11 March 2009 18:05 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
oh and thanks JRowbottom for the heads up on the flaw.

i've changed it and i've made sure the time has a leading 0 if its before 10am!

so either will work now!

the full query now reads

select * from filmshowing where showdate > to_char(SYSDATE, 'DD-MON-YY') or showdate = to_char(SYSDATE, 'DD-MON-YY') and to_date(showtime,'HH24:MI:SS') > to_date(to_char(sysdate,'hh24:mi:ss'),'hh24:mi:ss');



thanks

[Updated on: Wed, 11 March 2009 18:07]

Report message to a moderator

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391339 is a reply to message #391068] Wed, 11 March 2009 18:16 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem JRowbottom pointed out has been fixed for the time part but not the date part. You're comparing strings instead of dates.
By character ordering:
01-JAN-09 comes after 01-FEB-09

Why don't you combine showdate and showtime into a single date time and compare the result to sysdate.

Re: Date and Time comparison query - HELP (2 threads merged by bb) [message #391772 is a reply to message #391305] Fri, 13 March 2009 07:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wasn't intending to criticise your posts in this thread @michel - I only replied to your post as it was the last one in the thread at that time.

We have differences of opinion about what is an acceptable standard of OP, but I'm quite prepared to admit that your posts criticising posting style and content frequently contain the information that the OP is looking for.

The main thing that my pst added was, as you pointed out, more words.
I get the impression that the OP simply doesn't know why what he's doing is bad practice. Rather than have him blindly do something different, I feel it is worth explaining in more detail why one approach is better.

Plus my method still works if one of his films starts after midnight.
Previous Topic: Encountered the symbol "(" (merged 3)
Next Topic: About the invoker privilege
Goto Forum:
  


Current Time: Sat Dec 03 22:34:06 CST 2016

Total time taken to generate the page: 0.10227 seconds