[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

php timestamp

Posted on 2013-11-01
5
Medium Priority
?
325 Views
Last Modified: 2013-11-05
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

thanks
Jack
0
Comment
Question by:jack-lindsay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 1

Expert Comment

by:rhei
ID: 39616500
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

OR
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.
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 1000 total points
ID: 39616592
Should be as simple as this
SELECT * FROM table 
WHERE DATE(signIn) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) AND 
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.
0
 

Author Closing Comment

by:jack-lindsay
ID: 39624147
I got it sorted in the end, using your sql statement as a bases, i ended up with


SELECT *
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.

Thanks
Jack
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39624216
You are welcome - thanks for the points.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question