MySQL / PHP - Time Question

Question

In my DB I stored the time of the last page refresh in a TIME column. I want to compare the last page refresh to the current time now and find out how many 5 minuet periods have passed?

I was thinking take the last page refresh and subtract it from the current time then divide by 5. But I don't know about how things are formatted.

Help me please!

Any reason why you aren't using DATETIME instead of just TIME? — NullUserException
– NullUserException, Commented Sep 6, 2010 at 21:58
@shorty Of course you do. How else are you going to be able to tell if the time there is from today, yesterday or 1 year ago? — NullUserException
– NullUserException, Commented Sep 6, 2010 at 22:10
well if the current time is less than the last refresh i will assume a day has passed — user377419
– user377419, Commented Sep 6, 2010 at 22:22
Why assume a day has passed? What if actually 2 days passed? You are adding assumptions and complicating something that should've been simple (eg: just use a full date or a unix timestamp) — NullUserException
– NullUserException, Commented Sep 6, 2010 at 22:29

cichy · Accepted Answer · 2010-09-06 22:26:16Z

1

SELECT *, FLOOR(((UNIX_TIMESTAMP()-time_field)/300)) AS periods FROM table

Im not sure for if UNIX_TIMESTAMP() returns correct timestamp, you will have to check it ;)
EDIT: checked it myself and its working great ;)

edited Sep 6, 2010 at 22:26

answered Sep 6, 2010 at 22:00

cichy

10.7k4 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user377419 Over a year ago

im assuming unix_timestamp is the current time and time field is the last page refresh? sorry kinda new to php >.<

cichy Over a year ago

Yes, in this query you should only change name of the table, and "time_field" to name of the column you have your last refresh, and it should work right away.

user377419 Over a year ago

ok ill try it! thanks. but where does it tell me how many 5 min periods have passed?

cichy Over a year ago

I added name of the field where you will have 5 min periods counted, if you want to change it to 10 min etc, just change 300 to (60 * x) where x is minutes.

Collectives™ on Stack Overflow

MySQL / PHP - Time Question

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related