What"s wrong with this?

I need to find out whos birthdays land today. The DOB is stored in a
standard Unix timestamp. I'm trying to use the following but it throws up a
syntax error.

SELECT numberx FROM member WHERE date('d m',dob)=date('d m',time())

Where am I going wrong?

Re: What"s wrong with this?

"Mad Hatter" wrote in message
news:emsz5m7dx3wa$.1sbfzo9srgret.dlg@40tude.net...
>I need to find out whos birthdays land today. The DOB is stored in a
> standard Unix timestamp. I'm trying to use the following but it throws up
> a
> syntax error.
>
> SELECT numberx FROM member WHERE date('d m',dob)=date('d m',time())
>
> Where am I going wrong?

DATE() does not take any parameters - you're getting confused with PHP's
date, or SQL's DATE_FORMAT
TIME() only returns the time of day

Are you sure you have the dob as a Unix Timestamp? That would be weird.
Now for example would be "1192147732" in Unix Time.

It's more likely you have the dob as a DATE column-type, e.g. 1943-12-31

It that's the case, then use:

SELECT numberx FROM member WHERE DAYOFYEAR(dob)=DAYOFYEAR(NOW())

Have fun.

Re: What"s wrong with this?

On 11 Oct, 22:55, Mad Hatter wrote:
> I need to find out whos birthdays land today. The DOB is stored in a
> standard Unix timestamp. I'm trying to use the following but it throws up a
> syntax error.
>
> SELECT numberx FROM member WHERE date('d m',dob)=date('d m',time())
>
> Where am I going wrong?

In future, might I suggest that you post the text of the error!

Re: What"s wrong with this?

Hi

>
> DATE() does not take any parameters - you're getting confused with PHP's
> date, or SQL's DATE_FORMAT
> TIME() only returns the time of day

That's where I'm going wrong then. I'm using php commands on a sql query
:-(
>
> Are you sure you have the dob as a Unix Timestamp? That would be weird.
> Now for example would be "1192147732" in Unix Time.

Yup, definetely Unix timestamp. I prefer storing dates that way.
>
> It's more likely you have the dob as a DATE column-type, e.g. 1943-12-31
> It that's the case, then use:

Thanks but due to unix date no help :-(

Re: What"s wrong with this?

On 12 Oct, 11:34, Mad Hatter wrote:
> Hi
>
>
>
> > DATE() does not take any parameters - you're getting confused with PHP's
> > date, or SQL's DATE_FORMAT
> > TIME() only returns the time of day
>
> That's where I'm going wrong then. I'm using php commands on a sql query
> :-(
>
>
>
> > Are you sure you have the dob as a Unix Timestamp? That would be weird.
> > Now for example would be "1192147732" in Unix Time.
>
> Yup, definetely Unix timestamp. I prefer storing dates that way.
>
>
>
> > It's more likely you have the dob as a DATE column-type, e.g. 1943-12-31
> > It that's the case, then use:
>
> Thanks but due to unix date no help :-(

Well, let's look at this.

Most of us when using a tool like a database system choose the most
appropriate facilities, to ensure efficiency and ease of maintenance.
You think you know better and choose to store times as unix times.

Next, you have proved that you could not be bothered to check your
problem in the manual. If I get a syntax error, I go to the manual to
see what I've done wrong. There you would have seen that you were
using totally the wrong syntax and would have seen the correct one.

When I want to do something and I'm not sure how to, I try searching
Google or the manual.
Putting the words
mysql unix timestamp
into Google brings up the correct page of the mysql user manual as the
first hit. Going to that page (by the way, that'sthe page you would
already have been on had you bothered to llok up he syntax error) and
searching for UNIX, you would have hit straight upon the FROM_UNIXTIME
function.

So, not only do you "prefer" not to store times properly, you also
"prefer" not to bother looking at the manual or doing any other
obvious searches to help yourself. You "prefer" to get other people to
sort out your messes for you.

Re: What"s wrong with this?

"Mad Hatter" wrote in message
news:1jhtk4i2a7rma.pmfylopjl254$.dlg@40tude.net...
> Hi
>
>>
>> DATE() does not take any parameters - you're getting confused with PHP's
>> date, or SQL's DATE_FORMAT
>> TIME() only returns the time of day
>
> That's where I'm going wrong then. I'm using php commands on a sql query
> :-(
>>
>> Are you sure you have the dob as a Unix Timestamp? That would be weird.
>> Now for example would be "1192147732" in Unix Time.
>
> Yup, definetely Unix timestamp. I prefer storing dates that way.
>>
>> It's more likely you have the dob as a DATE column-type, e.g. 1943-12-31
>> It that's the case, then use:
>
> Thanks but due to unix date no help :-(

(Still don't know why you would store a dob as Unix Timestamp, but anyway)

..... simply convert your UNIX_TIMPSTMP to a normal date/time, using
FROM_UNIXTIME()

So, for example, to get today's day of the yea if it was a Unix Timestamp:

SELECT DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP()))

So, to solve your problem:

SELECT numberx FROM member
WHERE DAYOFYEAR(FROM_UNIXTIME(dob))=DAYOFYEAR(NOW())

Re: What"s wrong with this?

On Fri, 12 Oct 2007 11:08:56 -0000, Captain Paralytic wrote:
> Well, let's look at this.
>
> Most of us when using a tool like a database system choose the most
> appropriate facilities, to ensure efficiency and ease of maintenance.
> You think you know better and choose to store times as unix times.

I have my own reasons for storing as a Unix timestamp. I'm sure that at
some time in the past you may have done something that others (or even the
manual!) says is not the right way of doing things because it's far more
convenient.
>
> Next, you have proved that you could not be bothered to check your
> problem in the manual. If I get a syntax error, I go to the manual to
> see what I've done wrong. There you would have seen that you were
> using totally the wrong syntax and would have seen the correct one.

I did look at the manual but as it was late at night and I was tired I made
the mistake of looking at the php manual and not the mysql manual. But
there again I'm sure a Mr.Perfect like yourself is incapable of making such
mistakes.
>
I can't be bothered with the rest of your message. Your just not worth the
effort. Perhaps one day you might need help (but I doubt it seeing as you
know everything) and someone gives you such a helpful reply as the one you
gave me.

I'm just glad there are people like Virginner about who are wiling to help.

Re: What"s wrong with this?

On Fri, 12 Oct 2007 12:38:12 +0100, Virginner wrote:

Thanks for the reply. I've got everything worked out now :-)