[SOLVED] MYSQL Date and Time Functions

[d_barszczak]

Hi all,

 

Im am trying to further my mysql skills because i have been doing things the long way until now.

 

Ok, I have a table which stores my users. When my users register it records the date and time the account was created. Users can't login until they have confirmed their email address. The datecreated column is a DATETIME and i have another script executed from cron that i would like to delete all unconfirmed account that are older than 5 days old. The column that detimins whether that email is confirmed is called confirmed 1 for true 0 for false.

 

Im not sure how to do this even after reading the section on mysql's website.

 

Hope you can help.

[AdRock]

Have a look here at how they do the membership table

 

http://www.devarticles.com/c/a/PHP/Creating-a-Membership-System/1/

[toplay]

Something like this can be run every day in a cron job:

 

DELETE FROM `users`

WHERE 1

         AND `confirmed` = 0

         AND DATE(`datecreated`) = DATE_SUB(CURDATE(), INTERVAL 5 DAY)

;

[d_barszczak]

OK Thanks for your help.

 

What does the where 1 do?

[toplay]

OK Thanks for your help.

 

What does the where 1 do?

 

You don't need to do that, it's just out of habit that I use that notation.

 

You can do:

 

WHERE `confirmed` = 0

          AND DATE(`datecreated`) = DATE_SUB(CURDATE(), INTERVAL 5 DAY)

 

I try not to use "OR" conditions in MySQL queries (especially in versions earlier than 5) since it's inefficient (use UNION's instead). So most of my "where" clauses use "AND". I simply like it to line up by starting everything with "AND" (because it's easier for me to read), so I use "1" which is a true condition. That allows me to use "AND" on real first condition, so I can do this:

 

WHERE  1

          AND `column` .....

          AND `column` .....

          AND `column` .....

 

That's all.

 

Happy coding.

 

[d_barszczak]

Thanks!!

 

Another thing you might be able to help me with.

 

In the past i have used a seperate column for day month year so the when i design a report i could filter by month and year. Can i do anything like this with the date and time functions and would be any quicker to process?

 

This is really quite a weak area for me as my skills are more on the php side but working on a project which means that i need to strengthen my mysql side.

 

Thanks for all your help.

[fenway]

In the past i have used a seperate column for day month year so the when i design a report i could filter by month and year. Can i do anything like this with the date and time functions and would be any quicker to process?

You can use DATE_FORMAT() to get any piece you desire -- don't ever split up the date into pieces.

[d_barszczak]

I have been playing about all weekend with date and time functions an not only am i regretting all the time i have wasted programming the long way around but im also learning some awsome queries.

 

So now im getting adventurous.

SELECT DISTINCT month, count(month) as hits FROM hcl_counter WHERE `uid` = '5' GROUP BY month;

 

The above will generate me a list of hits per month for uid 5

 

MONTH HITS

October 89

November 320

 

In the same query can i generate an avarage Hits Per Month from the hits column. Can this sort of thing be done.

 

Once again thanks for all the help

 

[fenway]

You can use AVG() like COUNT()... but still, having a month column isn't ideal.

[d_barszczak]

lol, i posted the wrong query

 

Thats how the database works currently and trying to figure out how to convert it to the better method.

 

Thanks for all your help on this by the way.

 

Ill be back when i have converted my data.

[d_barszczak]

OK this is the big one.

 

Before i write a php script to do this for me is there any way to transform my day, month, year, minute and hour columns into the new date column?

 

 

[fenway]

Yes... STR_TO_DATE.

[d_barszczak]

OK thanks again.

 

How can i get the values from my columns to use in the STR_TO_DATE

 

Im sorry if i seem a little thick.

[fenway]

What is your table structure?

[d_barszczak]

currently i have a table thats like the following:

 

 

date  day  month   year   hour   minute   second

 

 

The date is currently something like 0000-00-00 00:00:00 for all records currently waiting to be populated.

 

The others are formatted like the following: 15 December 13 00 00

 

I want to populate the date column with the other information and then remove the old.

 

There are about 21,000 records.

 

I can do this with php but my mission to improove my mysql made me curious.

 

ps. the hitcounter script that populates the table has already been updated to use the new date method. Luckly i had not designed the reports system yet so not too much trouble.

[fenway]

I really hope you're not using all of those reserved keywords for your column names ;-(

 

You can simply use an update statement:

 

UPDATE yourTable SET theDateField = CONCAT( year, '-', month, '-', day, ' ', hour, ':', minute, ':', second )

[d_barszczak]

I thought it was ok as long as you used `column`!

 

But anyways it won't be a problem once i update the database as they will be eventually removed.

 

Thanks for all you help on this fenway.

 

Can i ask you a question? Are you self taught or did you study to learn mysql php?

[d_barszczak]

OK so talk about making a rod for your own back. I get the script that you sent me and have changed it to suit my needs and would work great if i had not done one stupid, silly, mistake.

 

I named the months ie November not 11 like i should have.

 

 

[Hooker]

It's a litle long winded but you can do this for each month:

 

UPDATE yourTable SET theMonthField='11' WHERE theMonthField='November';[/Code]

[fenway]

I thought it was ok as long as you used `column`!

Yes, but don't.

 

Can i ask you a question? Are you self taught or did you study to learn mysql php?

Never learned php -- and self-taught re: mysql.

 

OK so talk about making a rod for your own back. I get the script that you sent me and have changed it to suit my needs and would work great if i had not done one stupid, silly, mistake.

I named the months ie November not 11 like i should have.

That's too bad -- you can use a CASE statement to UPDATE all 12 months at once, and then copy over the raw date into the new field.

[d_barszczak]

UPDATE yourTable SET theMonthField='11' WHERE theMonthField='November';[/Code]

 

Right, right, right.. Here i go again.

 

The hitcounter scripts has only been running since October so i used Hooker's advice as only 2 months needed to be updated. That worked great.

 

Then i used Fenways advice to update the date column which now i has to change because seen as Fenway seems to be some sort of mysql god and i'd be stupid to ignore his advice date is still a reserved keyword and needs to be change (which i can do). Other that that its all good.

 

Thanks for all your advice. I have learned loads about the date and time functions im mysql which in a way was my goal. I should now be able to design some reports for my system using my new methods.

 

Thanks again to everyone who helped.

[fenway]

The easiest way to get around the reserved keyword issue is simply to have an underscore in your column name.  Good luck!

[d_barszczak]

you mean like _date for instance?

[fenway]

you mean like _date for instance?

That will work too... but it's a something_date, right?  Like registration_date, confirmation_date, purchase_date, etc.  Just make it descriptive and you won't ever run into this issue.

[d_barszczak]

Gotcha, need to start using the old noodle.

 

Thanks for all you help