SQL - MySQL - TimeStamp Query

ve

Hey, I have a MySQL database table with a column of the type "timestamp", which I am initializing with CURRENT_TIME when a row is created. All is fine there.

Now what I would love to be able to do and can't for the life of me figure out how to do it, is count the number of rows for each year/month group. For example if there are 7 entries made during 2007/Nov, 20 for 2007/Oct, etc I would like to be able to get the respective 7, 20 values back for a specified month.

Is this possible?

I appreciate any help I can get with this as my existing attempts whereby I was simply getting each row back and doing date/time calculations on every row returned (even though this didn't get me anywhere), when I know I should be able to get the query engine in MySQL to handle this kind of thing.

louie

you need to convert the date/time you want the entries counted to timestamp

http://blog.taragana.com/index.php/archive/how-to-process-mysql-timestamp-in-php/

ve

Thank you very much. That will serve useful.

Is there any way though that I can use the MySQL query engine to group by year/month, so I can get totals rows for each of the groups.

I'm sorry if I'm wrong, but the method you've linked to above will simply give me access to the data, by which I would still have to cycle through each row and access the date/time (correctly this time), and increment some month counter in my application code to get the number of entries for a given month.

Any ideas?

louie

can you give us the sql you have?

ve

The SQL I have is a straight run SELECT

SELECT UNIX_TIMESTAMP('time_column') FROM relevant_table

Now that gives me a date format that I can work with from PHP, no problem there. However I still have to go through every row myself using PHP code to extract the data, check it, and increment some array counter (for example) that will denote the number of monthly occurances.

Is there are SQL statement that I could use that would let me get the number of rows that have a 'time_column' value from a particular year/month.

Thanks for your help.

louie

Did you try converting the dates you want into
[php]
$date = strtotime(date("Y-m-d H:i:s"));

// the $date could come from a calendar or select menu

[/php]

then use ">=" "<=" operators to select only those records?

amen

you should be doing all your grouping in the database thats what a SQL database is for.

Have a look at the DateFormat function in MySQL.
you could extract the month/year that you want and then use GROUP to group the data

Eoin

Is this what you're looking for?

select Month(MyDate), count(Month(MyDate)) from test.testtable group by Month(mydate)

ve

@eoin_s: Absolutely spot on, thank you very much

I knew there had to be a way to get the query engine to do the work.

Cheers

Eoin

ve wrote: »

@eoin_s: Absolutely spot on, thank you very much

I knew there had to be a way to get the query engine to do the work.

Cheers

no bothers

ve

One more question (sorry)

SELECT Month(occurred) as month_index, count(Month(occurred)) as month_count FROM Referral
GROUP BY Month(occurred) where occurred >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

What is wrong with this?, I am trying to only SELECT rows that have a TIMESTAMP between now and 1 year ago. I am getting an "invalid syntax" return.

amen

your should really rtfm
but move the where statement to before the group statement

is this for a college project or work?

if work you should get them to send you on a database/sql course

bad sql design will haunt you later

ve

/slaps myself over head.

It's work I'm doing for myself. Yes I know this SQL is terrible, but this is why I'm here.

As regards a course, now that might actually be a good idea. Don't have an abundance of cash though and these things are generally quite expensive, but might invest in a good book, that will take me from the ground up.

Recommend a book?

FindingNemo

ve wrote: »

/slaps myself over head.

It's work I'm doing for myself. Yes I know this SQL is terrible, but this is why I'm here.

As regards a course, now that might actually be a good idea. Don't have an abundance of cash though and these things are generally quite expensive, but might invest in a good book, that will take me from the ground up.

Recommend a book?

hmm..not so pro-active eh :-P
I went to google and typed in mySQL SQL tutorial

http://www.webdevelopersnotes.com/tutorials/sql/index.php3

http://sqlzoo.net/


there are thousands out there !!!!

here's an example.


select INITIATIVE from BRAIN
where BRAIN='dont know the answer'

results returned
_____________

google


mad ted.

ve

select INITIATIVE from BRAIN
where BRAIN='dont know the answer'

Good job :rolleyes:

@FindingNemo: Just because I ask the question, doesn't mean I don't know how to find out. I'm just trying to save time. When someone else asks me questions I tend to pay them the same courtesy, so we all don't end up wasting time reinventing the wheel.

Hopefully this thread will serve someone else in the future, and that they will be able to find it. Given that I labeled it "SQL - MySQL - TimeStamp Query", gives it a little more context as opposed to "Help me!, my code won't work"

Simple as that. Cheers for the links

Evil Phil

FindingNemo, any more personaly abusive posts from you and you'll find yourself banned from the forum. The same goes for backseat modding.

FindingNemo

ve wrote: »

select INITIATIVE from BRAIN
where BRAIN='dont know the answer'

Good job :rolleyes:

@FindingNemo: Just because I ask the question, doesn't mean I don't know how to find out. I'm just trying to save time. When someone else asks me questions I tend to pay them the same courtesy, so we all don't end up wasting time reinventing the wheel.

Hopefully this thread will serve someone else in the future, and that they will be able to find it. Given that I labeled it "SQL - MySQL - TimeStamp Query", gives it a little more context as opposed to "Help me!, my code won't work"

Simple as that. Cheers for the links

You're right, I apologise,
I just think it looked like you could may have gone into slightly more research regarding tutorials etc.
Once again, apologies.

Eoin

ve, the queries you're trying to run don't look particularly specific to MySQL, so I'd say any basic SQL course will set you on your way. You may find a few differences in some keywords etc, but the basic premise will be the same.

I don't know any specific courses or books I can recommend, but you'll find a load of tutorials online as has been suggested.

sicruise

Evil Phil wrote: »

FindingNemo, any more personaly abusive posts from you and you'll find yourself banned from the forum. The same goes for backseat modding.

Thats not fair! You banned me before for just telling a guy to buy a book...

ve

eoin_s cheers for your advice

When I saw functions such as Month() I though I was executing MySQL specfic queries, hence the added confusion.

Thanks again.

Eoin

If keywords like that are specific to MySQL or whatever DB, you can be pretty sure there's a very similar syntax for another db.

evilhomer

eoin_s wrote: »

If keywords like that are specific to MySQL or whatever DB, you can be pretty sure there's a very similar syntax for another db.

Agreed, a lot of the newer DB's are relatively ANSI-SQL Compliant and have a most things are the same.

one thing that drove me mad though was that isnull() in mySQL does not do the same as in SQL Server

In mySQL it tells you if a field is null e.g. isnull(column_name), in SQL Server it's isnull(column_name, value_to_replace_null_with)

keep an eye out for stuff like that although there are usually tutorials to help you if you migrate from one SQL DB Engine to another.

Good luck with the SQL.