Organising Dates - Irish SEO, Marketing & Webmaster Discussion

ziycon · #1 (**permalink**) 26-02-2008, 09:44 PM

This is the first time i've seriously sat down to work with dates and times, i'm trying to check a DB table for any records based on a secondary key say userid then i want to take all these records and display them like so on a page:

March 2007 (0)
April 2007 (0)
June 2007 (0)

I want it to display a month from the first record record in the table for that user and the amount of records for that month up to and including the current month!
Any help as always appreciated!

Briask · #2 (**permalink**) 26-02-2008, 10:33 PM

Do you mean you want to display for a particular userid a count of records per month/year?

If so the the following will do it in MySQL, similar constructs will work in Oracle, MS-SQL, Sybase etc. Let me know if this helps?

SELECT

count(*) as rowCount,

monthname(lastModifiedDate) as month,

year(lastModifiedDate) as year

FROM myTableName
WHERE userid = myUserid
GROUP by year(lastModifiedMate), monthname(lastModifiedDate)
ORDER BY year(lastModifiedDate), monthname(lastModifiedMate)

Frodo · #3 (**permalink**) 26-02-2008, 10:59 PM

I would have done it like this but its the same thing really. I tested this code and it works (I use it), I haven't used those functions (year() and monthname() ) in MySQL before Briask are they commonly used?

Code:

SELECT DATE_FORMAT( `event-date` , '%M %Y' ) , COUNT( * )
FROM `events` WHERE `userid` = '1'
GROUP BY DATE_FORMAT( `event-date` , '%M %Y' )
ORDER BY DATE_FORMAT( `event-date` , '%M %Y' );

On a side note working these kind of things out is the best way to learn mysql I hope you had a go at it before asking here.

Briask · #4 (**permalink**) 26-02-2008, 11:17 PM

Just a matter of preferences... I just prefer to use explicit functions rather than using the date format strings MySQL :: MySQL 5.0 Reference Manual :: 10.6 Date and Time Functions covers all the date time functions you could ever need in MySQL.

ziycon · #5 (**permalink**) 26-02-2008, 11:23 PM

Quote:

Originally Posted by Frodo

I would have done it like this but its the same thing really. I tested this code and it works (I use it), I haven't used those functions (year() and monthname() ) in MySQL before Briask are they commonly used?

Code:

SELECT DATE_FORMAT( `event-date` , '%M %Y' ) , COUNT( * )
FROM `events` WHERE `userid` = '1'
GROUP BY DATE_FORMAT( `event-date` , '%M %Y' )
ORDER BY DATE_FORMAT( `event-date` , '%M %Y' );

On a side note working these kind of things out is the best way to learn mysql I hope you had a go at it before asking here.

I have had a go but i can't figure out how to take the dates from the record and the work with them, there all in the YYYY-MM-DD format.

Forbairt · #6 (**permalink**) 27-02-2008, 07:55 AM

Quote:

Originally Posted by ziycon

I have had a go but i can't figure out how to take the dates from the record and the work with them, there all in the YYYY-MM-DD format.

from the top of my head strtotime will take your Y-M-D and give you a unix timestamp

take this time stamp and use date on it

date("D M j G:i:s T Y",$myTimestamp);

something like that ... check out the php date functions

Forbairt · #7 (**permalink**) 27-02-2008, 08:03 AM

I should add mysql has a unix_timestamp function as well that I normally use. This just means I always get the date / datetime from the database in a useable format for me and avoids having to convert in php to a timestamp to play with it.

Frodo · #8 (**permalink**) 27-02-2008, 12:01 PM

Quote:

Originally Posted by ziycon

I have had a go but i can't figure out how to take the dates from the record and the work with them, there all in the YYYY-MM-DD format.

Did you try that query it extracts the date as month, year in fact it does exactly what you asked for.

MySQL has some very powerful date functions see the link Briask posted. Its much better to do all your date formating in your sql queries then in the PHP.

YYYY-DD-MM HH:MM:SS is the standard format for storing dates and is widely used avoids all that American vs European dates but the are other ways it can be stored, using the mysql date function in your query means you will always get the date format in the same way regardless of how it is stored in the db and then you won't break your php apps if it changes due to an update or changing the db.

Imagin if your converting all the dates you got from queries to unix format in your php and for some reason the date field in your db is shange to store them in unix time. What happens when you convert a unix time stamp to a unix time stamp.

ziycon · #9 (**permalink**) 27-02-2008, 01:45 PM

Nice one lads, i'll look at using the timestamp in my sql instead of php. It'll involve a lot of work to change all the existing data but might pay off in the long run before much more data is entered!

Forbairt · #10 (**permalink**) 27-02-2008, 01:59 PM

I wouldn't necessarily go changing the column type to a unix timestamp I'd just incorporate the unix timestamp function in your SQL ...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode