unix_timestamp()

[Sturnoff Megantic]

I have columns called repeat_date and repeat_month and I would like to
use the info within them in my WHERE clause as part of a
UNIX_TIMESTAMP( ).

Essentially, I would like to say something like:

SELECT * FROM table WHERE
UNIX_TIMESTAMP( repeat_date-repeat_month-$curYear 0:00:00) > $date_start
AND UNIX_TIMESTAMP( repeat_date-repeat_month-$curYear 0:00:00) <
$date_end

The variables $curYear, $date_start and $date_end will have been set
previously with PHP.

I'd like to use the information in each row within the
UNIX_TIMESTAMP( ).
Is this possible? If so, how would I do it?

Thanks in advance

[Thomas Bartkus]

Re: unix_timestamp( )

"Sturnoff Megantic" <gigantical@gma il.com> wrote in message
news:1106579244 .249160.73200@f 14g2000cwb.goog legroups.com...[color=blue]
> I have columns called repeat_date and repeat_month and I would like to
> use the info within them in my WHERE clause as part of a
> UNIX_TIMESTAMP( ).
>
> Essentially, I would like to say something like:
>
> SELECT * FROM table WHERE
> UNIX_TIMESTAMP( repeat_date-repeat_month-$curYear 0:00:00) > $date_start
> AND UNIX_TIMESTAMP( repeat_date-repeat_month-$curYear 0:00:00) <
> $date_end
>
> The variables $curYear, $date_start and $date_end will have been set
> previously with PHP.
>
> I'd like to use the information in each row within the
> UNIX_TIMESTAMP( ).
> Is this possible? If so, how would I do it?[/color]

UNIX_TIMESTAMP( $date) returns the number of seconds that have elapsed since
some time/date zero. I don't know/remember what time zero is and I don't
care since what I am usually looking for when I use UNIX_TIMESTAMP( ) is the
time difference (in seconds) between 2 different date/time values.

eg: Get records where $DateFinish occurs less than 60 seconds after
$DateStart
WHERE (UNIX_TIMESTAMP ($DateFinish) - UNIX_TIMESTAMP( DateStart)) < 60

Note that you do not need UNIX_TIMESTAMP( ) to compare 2 dates or to find
which date happened first or last. You can compare dates directly for that.
I can't quite decipher your example, but I sense you needn't even bother
with the UNIX_TIMESTAMP( ) function. Given valid date/time types, you can do
something like:
WHERE ($repeat_date > $date_start) AND ($repeat_date < $date_end)

Note that you needn't involve the UNIX_TIMESTAMP( ) function to do boolean
compares on dates. You only need the UNIX_TIMESTAMP( ) to report the actual
elapsed time (in seconds) between 2 dates.

Hope that helps.
Thomas Bartkus

[jmayoff@gmail.com]

Re: unix_timestamp( )

I've got the day of the month (repeat_date) and the month
(repeat_month) in seperate columns (they're not PHP variables), and I
want to create a date with them on the fly (within MYSQL) and make sure
it falls within a certain time period.

Consider a calendar with an event that repeats every year. I want to
compare a unix timestamp created with the day of the month and the
month of the year in the database and the current year (ie 2005) with
two other unix timestamps, essentially the range that I'll be showing
on my events list....

So, there's this event that comes up every 5th of January. I'll be
displaying a list of events that take place between Jan 1 and Jan 31.
So, I need a way to:

1. create a unix_timestamp equal to the 5th of Jan of this year (using
5 and 1(for Jan) from db columns) and ...
2. compare it to the 1st and 31st.

I think I can handle part two ... WHERE x > $startdate etc...

It's the first part, creating a unix timestamp to compare with the
$startdate etc... that I'm having trouble with. I'd like to be able to
refer to the repeat_date and repeat_month columns in the
UNIX_TIMESTAMP( ) to create the unix timestamp.... ie
UNIX_TIMESTAMP( 'repeat_date-repeat_month-2005 10:00:00')
Does that make any more sense?

[Sturnoff Megantic]

Re: unix_timestamp( )

I've got the day of the month (repeat_date) and the month
(repeat_month) in seperate columns (they're not PHP variables), and I
want to create a date with them on the fly (within MYSQL) and make sure
it falls within a certain time period.

Consider a calendar with an event that repeats every year. I want to
compare a unix timestamp created with the day of the month and the
month of the year in the database and the current year (ie 2005) with
two other unix timestamps, essentially the range that I'll be showing
on my events list....

So, there's this event that comes up every 5th of January. I'll be
displaying a list of events that take place between Jan 1 and Jan 31.
So, I need a way to:

1. create a unix_timestamp equal to the 5th of Jan of this year (using
5 and 1(for Jan) from db columns) and ...
2. compare it to the 1st and 31st.

I think I can handle part two ... WHERE x > $startdate etc...

It's the first part, creating a unix timestamp to compare with the
$startdate etc... that I'm having trouble with. I'd like to be able to
refer to the repeat_date and repeat_month columns in the
UNIX_TIMESTAMP( ) to create the unix timestamp.... ie
UNIX_TIMESTAMP( 'repeat_date-repeat_month-2005 10:00:00')
Does that make any more sense?

[Bill Karwin]

Re: unix_timestamp( )

You can use DATE() to convert a string to a true date value.
You can use CONCAT() to make an expression that results in a string
formatted like a date literal.

Two terms in that expresion are column names in your database table, and
one is the $curYear PHP variable, which is substituted in at the time
you build the string for your whole SQL query.

SELECT ...
FROM `table`
WHERE DATE(CONCAT(rep eat_date, '-', repeat_month, '-', $curYear)) >
DATE('$date_sta rt') ...

$curYear is an integer, and I assume so are the two columns. The
expression will implicitly convert the integers to their string
representation in order to do the concatenation. Also, there's no need
to list '00:00:00' for the time portion; this is the default value for
MySQL date literal expressions.

Note also that your PHP variable $date_start functions as a date string
literal, so it needs to be in quotes.

Building SQL strings in an application language is a two-step process:
one step for interpreting the host language (PHP in this case), and one
step for the SQL engine to parse the resulting query string. MySQL
doesn't see the string until you pass it on. This means that after all
the variable substitutions have been made by PHP, the string needs to be
a legal SQL query.

I think the UNIX_TIMESTAMP( ) function isn't the correct one to use in
your case, unless the $date_start value is in UNIX time format, that is
an integer of seconds since 1/1/1970.

Regards,
Bill K.

[Sturnoff Megantic]

Re: unix_timestamp( )

Thank you. CONCAT() is exactly what I was looking for. It works like
a charm. And, yes $date_start is in unix timestamp format. I find it
much easier to deal with dates that way. So much easier to compare and
do math on etc.

Thanks again.... in the end my SQL looked like this:

SELECT * FROM events WHERE (date BETWEEN $date_range_sta rt and
$date_range_end ) OR (UNIX_TIMESTAMP (CONCAT($curYea r, '-', repeat_month,
'-', repeat_date)) BETWEEN $date_range_sta rt AND $date_range_end )

SM