Menu
Community /Pin to ProfileBookmark
@NogDogJul 03.2013 — #If you're storing it as an integer, then I suppose you could use modulus (MOD() function in MySQL), although any such solution will require a full table scan (indexes won't help).
@NogDogJul 08.2013 — #Just to make sure we're on the same page, what is the actual type for the epoch field, and maybe include some sample raw data (e.g.: "SELECT epoch FROM table_name LIMIT 20;")? @NogDogJul 08.2013 — #As far as I can see, that should work. It so happens that the sample data you output does not contain any times that have a '00' for the seconds part, so none of those would match (unless you want to ignore the seconds and only look at the minutes part?):
Output:
Get row where epoch equals zero seconds
Hi, I would like to know if there is a way to get row results where the stored epoch time is matched to the quarter hour and zero seconds. Like this:
12:00:00
12:15:00
12:30:00
12:45:00
01:00:00
01:15:00
01:30:00
01:45:00
And any results that are like this:
12:00:02
12:25:00
Would be skipped. Thank you!
Sign in
to post a comment5 Comments(s) ↴
0
@NogDogJul 03.2013 — #If you're storing it as an integer, then I suppose you could use modulus (MOD() function in MySQL), although any such solution will require a full table scan (indexes won't help).<i>
</i>. . . WHERE MOD(column_name, 15*60) = 0 . . .
0
@NogDogJul 08.2013 — #0
@NogDogJul 08.2013 — #As far as I can see, that should work. It so happens that the sample data you output does not contain any times that have a '00' for the seconds part, so none of those would match (unless you want to ignore the seconds and only look at the minutes part?):[code=php]
<pre>
<?php
$testData = array(
1373261982,
1373261401,
1373261180,
1373260729,
1373260501,
1373259601,
1373258963,
1373258701,
1373258477,
1373258269,
1373257801,
1373257703,
1373257520,
1373256901,
1373256750,
1373256364,
1373256002,
1373255741,
1373255360,
1373255102,
);
foreach($testData as $test) {
printf(
"Remainder of %d %% (15*60) = % 3d (date: %s)".PHP_EOL,
$test,
$test % (15 * 60),
date('Y-m-d H:i:s', $test)
);
}
?>
</pre>[/code]Output:
<i>
</i>Remainder of 1373261982 % (15*60) = 582 (date: 2013-07-08 01:39:42)
Remainder of 1373261401 % (15*60) = 1 (date: 2013-07-08 01:30:01)
Remainder of 1373261180 % (15*60) = 680 (date: 2013-07-08 01:26:20)
Remainder of 1373260729 % (15*60) = 229 (date: 2013-07-08 01:18:49)
Remainder of 1373260501 % (15*60) = 1 (date: 2013-07-08 01:15:01)
Remainder of 1373259601 % (15*60) = 1 (date: 2013-07-08 01:00:01)
Remainder of 1373258963 % (15*60) = 263 (date: 2013-07-08 00:49:23)
Remainder of 1373258701 % (15*60) = 1 (date: 2013-07-08 00:45:01)
Remainder of 1373258477 % (15*60) = 677 (date: 2013-07-08 00:41:17)
Remainder of 1373258269 % (15*60) = 469 (date: 2013-07-08 00:37:49)
Remainder of 1373257801 % (15*60) = 1 (date: 2013-07-08 00:30:01)
Remainder of 1373257703 % (15*60) = 803 (date: 2013-07-08 00:28:23)
Remainder of 1373257520 % (15*60) = 620 (date: 2013-07-08 00:25:20)
Remainder of 1373256901 % (15*60) = 1 (date: 2013-07-08 00:15:01)
Remainder of 1373256750 % (15*60) = 750 (date: 2013-07-08 00:12:30)
Remainder of 1373256364 % (15*60) = 364 (date: 2013-07-08 00:06:04)
Remainder of 1373256002 % (15*60) = 2 (date: 2013-07-08 00:00:02)
Remainder of 1373255741 % (15*60) = 641 (date: 2013-07-07 23:55:41)
Remainder of 1373255360 % (15*60) = 260 (date: 2013-07-07 23:49:20)
Remainder of 1373255102 % (15*60) = 2 (date: 2013-07-07 23:45:02)