mySQL query query

[Lord-Nikon]

SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now, 
UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens, 
UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes
FROM random_table
WHERE UNIX_now <= UNIX_closes AND UNIX_now >= UNIX_opens

Problem : UNIX_now doesn't exist as a column.

Tried :
1. WHERE `UNIX_now` ....
2. WHERE tablename.UNIX_now
3. WHERE [UNIX_now]

Can someone please be so kind as to explain to me why I can't do this and possibly provide a solution.

I ended up doing :

SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now, 
UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens, 
UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes 
FROM random_table
WHERE UNIX_TIMESTAMP(NOW()) <= UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) 
AND UNIX_TIMESTAMP(NOW()) >= UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time))

 

[dequadin]

Maybe I'm missing something but there is no FROM clause in your query?

SELECT [...,...]
[b]FROM [][/b]
WHERE []

 

[Lord-Nikon]

Maybe I'm missing something but there is no FROM clause in your query?

SELECT [...,...]
[b/]FROM [][/b]
WHERE []

Yes, I neglected to put it in because I don't feel like showing what I'm working on The problem at hand is not the FROM clause, it's the WHERE clause

EDIT: Ok, for arguments sake it's in now...

 

[rorz0r]

Not sure about mySQL (it's been a while) but in MS SQL you can't use "aliases" further down in your query, so you have to use whatever the original was. You can kinda get around it by using inner queries or temp tables though if you have some hectic calculations etc.

 

[Lord-Nikon]

Not sure about mySQL (it's been a while) but in MS SQL you can't use "aliases" further down in your query, so you have to use whatever the original was. You can kinda get around it by using inner queries or temp tables though if you have some hectic calculations etc.

Crap, suspected something like that. I might just end up putting this into a STORED PROCEDURE for future reference. This query is going to get called a lot and will always stay the same. Results will vary, but query not. The rest (that I've left out of the query) kinda gets a bit hectic.

 

[guest2013-1]

Crap, suspected something like that. I might just end up putting this into a STORED PROCEDURE for future reference. This query is going to get called a lot and will always stay the same. Results will vary, but query not. The rest (that I've left out of the query) kinda gets a bit hectic.

Yes, aliasing is normally okay in the select portion (MS SQL example):

select u.UserID,(select AnotherUserID from Users u2 where u2.AnotherUserID = u.UserID)
from Users u

What you might consider is using the "into" command to load the selected values into a temporary table, which would then create the column UNIX_now correctly so you could then use the where statement as required.

I would suggest however that you do most of your "where" stuff within the statement that will select into the temporary table otherwise you might end up with performance issues.

 

[dequadin]

Not sure about mySQL (it's been a while) but in MS SQL you can't use "aliases" further down in your query, so you have to use whatever the original was. You can kinda get around it by using inner queries or temp tables though if you have some hectic calculations etc.

+1 for you can't use "aliases" further down in your query.

Have you Googled "mySQL using aliases in where clause"? Because you can't do this either in MySQL

 

[Lord-Nikon]

+1 for you can't use "aliases" further down in your query.

Have you Googled "mySQL using aliases in where clause"? Because you can't do this either in MySQL

Meh!

Thanks for everybody's input. Guess its back to good old typing out everything...

 

[guest2013-1]

Meh!

Thanks for everybody's input. Guess its back to good old typing out everything...

Or using my method

Copy/Paste FTW!

 

[rorz0r]

SELECT *
FROM (
	SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now, 
	UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens, 
	UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes
	FROM random_table
) a
WHERE UNIX_now <= UNIX_closes AND UNIX_now >= UNIX_opens

 

[Lord-Nikon]

Or using my method

Copy/Paste FTW!

LOL! C/P is being used like crazy at the moment. Perhaps we could suggest this to the creators of MySQL? You never know, in the next release this might get supported

 

[Other Pineapple Smurf]

Solution:

SELECT * FROM (
SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now,
UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens,
UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes
FROM random_table ) foo
WHERE UNIX_now <= UNIX_closes AND UNIX_now >= UNIX_opens

 

[Other Pineapple Smurf]

SELECT *
FROM (
	SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now, 
	UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens, 
	UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes
	FROM random_table
) a
WHERE UNIX_now <= UNIX_closes AND UNIX_now >= UNIX_opens

Cr@p, you beat me too it

 

[FarligOpptreden]

If I were to do it in MSSQL, I would've put the timestamp data in a view... Simple, elegant and fast.

 

[Lord-Nikon]

If I were to do it in MSSQL, I would've put the timestamp data in a view... Simple, elegant and fast.

Sadly/Gladly no MSSQL

 

[Lord-Nikon]

SELECT *
FROM (
	SELECT UNIX_TIMESTAMP(NOW()) AS UNIX_now, 
	UNIX_TIMESTAMP(CONCAT(opens_date,' ',opens_time)) as UNIX_opens, 
	UNIX_TIMESTAMP(CONCAT(closes_date,' ',closes_time)) as UNIX_closes
	FROM random_table
) a
WHERE UNIX_now <= UNIX_closes AND UNIX_now >= UNIX_opens

Excellent! Thank you!