Solved

php timestamp

Posted on 2013-11-01
5
316 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 52

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 52

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php ssh2_scp_send 1 48
Is this error or Notice in php error log? 6 32
Should I use subdomains or addon domains? 3 35
WordPress website error - Unable to access website. 5 31
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

910 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

21 Experts available now in Live!

Get 1:1 Help Now