Solved

php timestamp

Posted on 2013-11-01
5
315 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
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 51

Accepted Solution

by:
Julian Hansen earned 250 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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39616678
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 51

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses four methods for overlaying images in a container on a web page
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 look for a specific file type in a local or remote server directory using PHP.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now