CODEWITHSUNDEEP
phpsql
tomexsans
tomexsans

Reputation: 4527

getting the right week and year from a timestamp

i have a function which gets all the newly registered users, and displays it depending on the filter given whether it is newly registered by month or by year.

on this example i am using year, i have this sql

Select date_registered from users where date_registered > '2011-12-31 12:59:59' AND date_registered < '2013-01-01 12:00:00'

so this will result in all date_registered registered for the year 2012

the question is how to be more dynamic that i may apply a filter for example where date_registered > $dynamic_date and date_registered < $dynamic_date

or any good advise how to achieve this better.

thanks . .

Upvotes: 0

Views: 58

Answers (4)

nyson
nyson

Reputation: 1055

You should use BETWEEN, it's more readable.

MySQL has a great list of date and time functions

Basically, what you want to do is this: 

SELECT *
FROM Store_Information
WHERE Date BETWEEN '$start' AND '$end'

I don't know what data you're starting from, or where you get your $start and $end inputs. I'm going to assume you're using unix timestamps, generated from time(), and then you can do something like this:

SELECT *
FROM Store_Information
WHERE Date BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end')

If this does not suffice, please be clearer how you format your indata ($start, $end).

Upvotes: 1

Corbin
Corbin

Reputation: 33457

If it's a small data set, I would go the pure SQL route that Tikkes' answer shows; however, for index purposes, calculating the bounds may be better.

mktime and date can be used for this:

$lowerBound = date('Y-m-d H:i:s', mktime(0, 0, -1, 1, 1, date('Y')); //1 second before 00:00:00 1 Dec <current year>
$upperBound = date('Y-m-d H:i:s', mktime(0, 0, -1, 1, 1, date('Y') + 1); //1 second before 00:00:00 1 Dec <current year + 1>

So then you just put them in a query:

$sql = " ... WHERE d > '$lowerBound' AND d < '$upperBound'";

Upvotes: 1

Tikkes
Tikkes

Reputation: 4689

I believe this is what you are looking for:

$sql = "SELECT date_registered 
       FROM users
       WHERE YEAR(date_registered) = YEAR(NOW())";

check this out for more information

Since you also want the right week, also look at the function YEARWEEK(). It works in the same way and returns the number of the week as well. See here

Upvotes: 2

elgris
elgris

Reputation: 526

try this code

$sql = "SELECT date_registered FROM users WHERE date_registered > '$dateStart' AND date_registered < '$dateEnd'";

where $dateStart and $dateEnd are your dynamic date variables

A pair of SINGLE quotes around your variables should be enough. All the statement string has to be in DOUBLE quotes as you see.

Upvotes: 1

Related Questions