[RESOLVED] SQL Query with individual date columns

_sh

I have a DB with three columns, Date, Month and Year, for example in the following formats...

Day, Month, Year
20 12 2008

In my SQL query, I want to somehow compare these to the current date so as to ONLY show rows where the date is AFTER the current date (removing old events).

I also want to use Interval to offset the USA time that my MySQL server is set to.

PHP/MySQL and dates really confuse me, it all seemed so easier in ASP?

I appreciate your help!

p.s. Does anyone else find this forum to be painfully slow at rendering?

coldwerturkey

Why would you separate the date like that?.. that's silly.

Putting the date as a timestamp or YYYYMMDD you could do the query very very simply with basic operators (=, >, <, etc) in your WHERE clause.

_sh

I agree entirely but its the 'getting it back into PHP' that just confuses me - from that timestamp how would I then grab perhaps just the Month integer in PHP?

coldwerturkey

if you use a timestamp, there's query functions that can extract just the date. with PHP you could use an assortment of functions like date('m', $timestamp), substr_replace(), etc. to get the month or day or year.

There's probably a way to merg rows in a query ie (make YYYYMMDD from the three), and I'm sure someone will post it.. I just think it's weird.

_sh

coldwerturkey;10908514 wrote:
if you use a timestamp, there's query functions that can extract just the date. with PHP you could use an assortment of functions like date('m', $timestamp), substr_replace(), etc. to get the month or day or year.

There's probably a way to merg rows in a query ie (make YYYYMMDD from the three), and I'm sure someone will post it.. I just think it's weird.

But a PHP TimeStamp is different to a MySQL Timestamp I believe - I think thats where the problem lies, so you've got to convert that first.

coldwerturkey

ashleyhall;10908522 wrote:
But a PHP TimeStamp is different to a MySQL Timestamp I believe - I think thats where the problem lies, so you've got to convert that first.

if you use unix timestamp you can "convert" with date('whatever-format', $timestamp),

but I'm referring to mysql's yyyy-mm-dd hh:mm:ss, where you can extract with PHP by using something like, date('m', strtotime($timestamp))

Weedpacket

coldwerturkey wrote:
but I'm referring to mysql's yyyy-mm-dd hh:mm:ss, where you can extract with PHP by using something like, date('m', strtotime($timestamp))

Assuming you didn't want to use the date and time functions the dbms would provide for working with its own date/time types.

ashleyhall wrote:
it all seemed so easier in ASP?

I doubt you were storing your dates in such a wacky way in whatever dbms you were using with ASP.

_sh

Weedpacket;10908781 wrote:
Assuming you didn't want to use the date and time functions the dbms would provide for working with its own date/time types.

I doubt you were storing your dates in such a wacky way in whatever dbms you were using with ASP.

I'm not using a DBMS its a bespoke application for showing events.

If I migrate the app to using MySQL Timestamp formatted strings for storing the dates, how in PHP do I then take the MySQL timestamp and do the following...

Just show the day integer from the timestamp
Just show the month integer from the timestamp
Just show the year integer from the timestamp
Convert it so as to compare two timestamps in PHP for before/after

I appreciate your help mate.

Weedpacket

Consult the MySQL manual for information on its date/time types and the functions it provides for working with them. See also the PHP manual for its date and time manipulation facilities.

Roger_Ramjet

There is absolutely no reason or excuse to get into UNIX timestamps when using mysql. With any rdbms just use the native date time data types. The you have the use of all of the sql date time functions and comparison operators. With mysql you can also use convert-tz function to handle your timezone requirements.

_sh

I've seen the light and spent today converting the site to use a single Timestamp MySQL column for all date formats - as many have commented in this thread it has made things much simpler, and I've finally got to grips with StrToTime and Date functions.

Thanks guys.

Roger_Ramjet

Just so long as you understand the difference between timestamp and datetime data types. Basically you use datetime for all date and time columns except when you specifically require a timestamp.

_sh

Roger Ramjet;10908950 wrote:
Just so long as you understand the difference between timestamp and datetime data types. Basically you use datetime for all date and time columns except when you specifically require a timestamp.

I was told that TimeStamps are more flexible so I'm using those for any instance of date, rather than the Date type? Is this going to cause issues?

Roger_Ramjet

RTFM

Timestamps are NOT more flexible. Whoever told you that does not know their stuff.

Timestamps have specific properties for a specific purpose: they can auto-update when the row is updated; they convert between Coordinated Universal Time/GMT and local timezone on storage and retrieval, which can be very inconvenient unless you want it; can never contain NULL. All of this makes them very specific and a lot less flexible.

You use Date or Datetime except when you need one of the special behaviours of Timestamp

_sh

Thanks for your reply. I'm not seeing any peculiarities in using Timestamps, but to move to the more flexible Date format, could I simply change the field type? Whenever writing to the current field, I'm using either NOW or using the xxxx-xx-xx format?