LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 05-16-2015, 09:03 PM   #1
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: Arch
Posts: 396

Rep: Reputation: 22
Question a SQL query question


This might be the wrong place to ask but I have no other ideas.

I know SQL a lot but I just can't get this thing with mySQL(MariaDB) to work,
probably since my knowledge about the syntax ain't at 100%.
My problem is to get a functional MariaDB-query with date fields.
I also have a isdiabled-field but that is no problem.

The fields are a fromDate and a toDate and are both nullable.
The problem is how I handle if these date-fields is 'null', not 'null' or some of it.

Think of it like this;
Both these dates can be 'null' and if so that the row should be gathered.
If there ain't a start date then only the end date would be stopping the query
and vice versa, all towards current date.
(NOT TIME!!! just the date)

If this was in MSSQL I would fix it, no problem, but I do not know all the syntax of MariaDB
to get this to work. I've googled a lot but probably not using the correct words for that either.

Please help an old man :P
 
Old 05-16-2015, 10:04 PM   #2
New2Linux2
Member
 
Registered: Jan 2004
Location: Arizona
Distribution: Debian
Posts: 153

Rep: Reputation: 43
Hello Basher52,

As far as I know, MariaDB takes the same SQL commands that MSSQL, MySQL or any other SQL database uses. It's all SQL.

You might get a more intelligent response if you post the SQL command that you're having trouble with. Reading over your post, I'm not following what exactly it is that you are trying to do with the data in the database. Just query it or massage it a little? Are you wanting to use functions that are specific to MariaDB and not standard SQL commands? Help us out with a bit of detail and perhaps a code snippet.
 
Old 05-16-2015, 10:09 PM   #3
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
I don't understand your condition "all towards the current date". But other than that, I think you should go into this direction:
Code:
SELECT foo FROM bar WHERE (NOT ISNULL(fromDate) and NOT ISNULL(toDate) AND mydate >= fromDate AND mydate <= toDate) OR
(ISNULL fromDate AND NOT ISNULL (toDate) AND mydate <= toDate) OR
(NOT ISNULL fromDate and ISNULL toDate and mydate >= fromDate);
jlinkels
 
2 members found this post helpful.
Old 05-17-2015, 10:58 AM   #4
Pearlseattle
Member
 
Registered: Aug 2007
Location: Zurich, Switzerland
Distribution: Gentoo
Posts: 999

Rep: Reputation: 142Reputation: 142
Quote:
Originally Posted by New2Linux2 View Post
Hello Basher52,

As far as I know, MariaDB takes the same SQL commands that MSSQL, MySQL or any other SQL database uses. It's all SQL.
Unluckily it's definitely not like that - in reality each engine needs often different commands/syntax and performs differently especially with date fields.

jlinkels' answer is an alternative.

Another one would be to use a "case"-condition., which is like an if-else (or if, else-if, else-if, ...., else) and which I know from Oracle but I did not use yet with MariaDB. The advantage here is that they're easy to read (if you don't overdo it )

Please keep in mind that at least with MariaDB & its main InnoDB engine, declaring a field as not-"NOT NULL" will impact a lot performance (HDD I/O).
 
1 members found this post helpful.
Old 05-17-2015, 11:07 AM   #5
New2Linux2
Member
 
Registered: Jan 2004
Location: Arizona
Distribution: Debian
Posts: 153

Rep: Reputation: 43
Wink

Quote:
Originally Posted by Pearlseattle View Post
Unluckily it's definitely not like that
Thanks for the clarification. I was under the impression that SQL was the standard language/command subset used by all SQL databases, and the differences came into play with their own unique way of doing things, like PostgreSQL's use of slash commands. I appreciate the correction to an erroneous assumption.
 
Old 05-17-2015, 11:28 AM   #6
Pearlseattle
Member
 
Registered: Aug 2007
Location: Zurich, Switzerland
Distribution: Gentoo
Posts: 999

Rep: Reputation: 142Reputation: 142
Quote:
Originally Posted by New2Linux2 View Post
Thanks for the clarification. I was under the impression that SQL was the standard language/command subset used by all SQL databases, and the differences came into play with their own unique way of doing things, like PostgreSQL's use of slash commands. I appreciate the correction to an erroneous assumption.
Eeehhhfffpppp..., you're welcome - you're actually like me 5 years ago

Unluckily it's messy - on one side there are different versions of the SQL-standard, on the other side the DB-producers create own additions and/or variations to commands.

I have some experience with sqlite/Oracle/mysql/mariadb/db2/postgresql (no mssql ) and for me the absolute nightmare is always to extract & import data between these engines => would be great to have a common channel!!

Another area which is really wild is the one that involves "hints". There everybody does whatever it wants and it varies between extremes (e.g. postgresql being the most "pure" and oracle the dirtiest but as well being the most loved in this category).

Table creation SQLs vary as well a lot because of all the possible options (where mariadb is probably the most extreme as its options vary depending on the storage engine that will be used).

Summarized:
doesn't matter what you're programming: when writing your code always try to abstract the commands/functions from what is really used against the DB.

Last edited by Pearlseattle; 05-17-2015 at 11:30 AM.
 
1 members found this post helpful.
Old 05-17-2015, 04:01 PM   #7
Basher52
Member
 
Registered: Mar 2004
Location: .SE
Distribution: Arch
Posts: 396

Original Poster
Rep: Reputation: 22
Thanks all, nice little discussion here too and I agree with Pearlseattle, all DBs I've been in contact with are always different if someways.
jlinkels, I definitely will try that and maybe add some to it, thx
and Pearlseattle, I'll see if I can use that case-condition too if I must.


thx again

Last edited by Basher52; 05-17-2015 at 04:03 PM.
 
  


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
sql query aocferreira Programming 2 05-13-2011 03:58 AM
sql query help struct Programming 2 10-27-2010 12:29 AM
sql query question kpachopoulos Programming 5 09-10-2007 12:54 PM
SQL query question Kamikazee Programming 2 10-31-2006 10:27 AM
SQL Query question oulevon Programming 7 01-16-2004 01:50 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 01:49 AM.

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
Open Source Consulting | Domain Registration