CODEWITHSUNDEEP
mysqlsqldate
d0m1n1c
d0m1n1c

Reputation: 157

How can I have an SQL query return an extra column with an arithmetic result in it?

My current query is

$sql="SELECT * FROM events, times 
WHERE events.email = '$email' 
and 
events.type = times.type";

events.date is a date in sql of form yyyy-mm-dd

times.time is a date in sql of the same form but 0000-mm-dd.

I want an extra column from my query with the recommended start dates for an event.

ie. events.date - times.time

Can I have sql return an extra column that includes the recommended start dates?

or am I way off the mark?

Any thoughts are appreciated.

edit:

this is what I have now:

SELECT *, date_diff(events.date, times.time) AS recommended FROM events, times WHERE events.email = '$email' and events.type = times.type ORDER by events.date

from what I can tell it shouldn't fail, but it does. it complains about my syntax.

edit:

it's working now, thanks

this is what I've got

$sql="SELECT *, date_format(events.date - times.time, '%Y-%m-%d') as recommended 
FROM events, times 
WHERE events.email = '$email' 
and 
events.type = times.type ORDER by events.date";

Upvotes: 0

Views: 1005

Answers (1)

Adam
Adam

Reputation: 1755

You're not off the mark at all. You can create an extra column and give it a name by doing your calculation and using an alias to name it.

SELECT *, TIMESTAMPDIFF(events.date,times.time) AS recommended_start_date
FROM events, times;

I'm not sure if the TIMESTAMPDIFF function is the exact one you're looking for. Perhaps DATEDIFF() might be better. This part of the MySQL docs lists all the date-related functions that you can use.

Upvotes: 5

Related Questions