• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

php timestamp

Hi Guys,

i've created a simple web app, for tracking the duration of time people have been signed in.  it uses the unix timestamp.

i've been asked to create a daily report which a user will run the next morning which shows who signed in after a 15:15

im having trouble and cant get my brain into gear to figure this one out.

i know its going to be SELECT * from table where 'signIn' >= theTimeStamp

but i cant figure out the timestamp calculations for counting back to 15:15 everyday.

can anyone help? i hope that makes sence.  if not i'll try and clarify

1 Solution
OK just a few clues:
1. the sentence "who signed in after 15:15" is wrong. Everybody signed in after 15:15, since even the first-ever user, who signed in lets say on 14:34 on 1st of November 1990 has signed after 15:15 the 31st October 1990, but he also has signed in after 15:15 of the 1st January 1970. If the sentence can be reformulated then you may get something like this: A) "who signed in after 15:15 before the start of the working hours the next day at 8:00 ?" or B) "who signed in after 15:15 until midnight that day?"

2. you did not specify, which database engine you are using. However if you DB engine has functions to extract the various parts from the unix timestamp (such as the "hours" part, the "minutes" part and so on), and then it will definitely have a function to do the opposite, then you may use an SQL (here in abstract form for the example 1B):
SELECT * FROM table WHERE table.TheTimeStamp>createTimeStampYMDHmS(getPart(table.TheTimeStamp,"Y"), getPart(table.TheTimeStamp,"M"),getPart(table.TheTimeStamp,"D"),15,15,0)

Open in new window

SELECT * FROM table WHERE getPart(table.TheTimeStamp,"H")>15 OR (getPart(table.TheTimeStamp,"H")=15 AND getPart(table.TheTimeStamp,"min")>15)

Open in new window

(here in abstract form for the example 1A):
SELECT * FROM table WHERE getPart(table.TheTimeStamp,"H")>15 OR getPart(table.TheTimeStamp,"H")<8 OR (getPart(table.TheTimeStamp,"H")=15 AND getPart(table.TheTimeStamp,"min")>15) 

Open in new window

To make the story short: you need the support of these operations in DB so you need to find the real equivalents of the functions as described above, since the operation has to be allways done for each line by the RDBMS.
If your DB does not provide such feature, then there are following options:
1. write yoursef these functions - inspect the exact definition of UNIX time stamp and create the functions for your DB
2. convert (by a one-time PHP script) the values into more columns such as (for this task only) "Hours" and "Minutes" and then use just a simple compare as written above
3. To read up the whole table within the script and make the compare ase stated above as an in-memory operation using the PHP functions for working with the Unixtimestamp, as optimization you may do this only for timestamp of the last 24 hours and schedule the script for daily use.
Julian HansenCommented:
Should be as simple as this
SELECT * FROM table 
TIME(signIn) > '15:15';

Open in new window

In other words - take 1 day off todays date and find all records that match that date with a time value > 15:15.
jack-lindsayAuthor Commented:
I got it sorted in the end, using your sql statement as a bases, i ended up with

FROM MyTable
WHERE DATE( FROM_UNIXTIME( MyField,  '%Y-%m-%d %H:%i:%S' ) ) = DATE( DATE_SUB( NOW( ) , INTERVAL 1 DAY ) )
AND TIME( FROM_UNIXTIME( MyField,  '%Y-%m-%d %H:%i:%s' ) ) >  '15:10:00'

Not sure if this could be simplified, but it works.

Julian HansenCommented:
You are welcome - thanks for the points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now