filter date from timestamp field in mysql

Question

I have saved rows in my table with custom timezone.Now I want to retrieve data from those table for just today.

So here is what I tried

date_default_timezone_set('America/Los_Angeles'); 
$dt = new DateTime();
$today = $dt->format('Y-m-d'); //outputs 2015-12-07
$ok = mysqli_query($sqli,"SELECT * FROM `table` WHERE `date` = '$today'" );

And my row contains date in timestamp format like 2015-12-07 22:42:02

But I get empty result.

Can you show us the code for the `outputs` function? I assume this is a function you have written yourself. — br3nt, Dec 12 '15 at 04:43
@br3nt : its just mysqli_query , nothing wrong in script...I ran the query directly in phpmyadmin..its returning empty results. — Vishnu, Dec 12 '15 at 04:46
Ok. Is the date column definitely a date data type or is it a datetime/timestamp? — br3nt, Dec 12 '15 at 04:47
well I tried somehting like this and it works $date = $dt->format('Y-m-d')." 00:00:00"; and `date` > $date ... some one answer it so i will accept. — Vishnu, Dec 12 '15 at 05:02
***@Vishnu***: What is the output when tried with `... where year( date_field ) = 2015 and month( date_field ) = 12` ??? — Ravinder Reddy, Dec 12 '15 at 06:22

Pathik Vejani · Answer 1 · 2015-12-12T05:08:48.673

1

Try this:

$ok = mysqli_query($sqli,"SELECT * FROM `table` WHERE DATE(date) = CURDATE()" );

to convert time according to timezone: ConvertTimeZone

edited Dec 12 '15 at 05:08

answered Dec 12 '15 at 04:53

Pathik Vejani

4,263
8
57
98

if possible then also change your column name `date` to other, because `date` is reserved keyword of `MySQL` – Pathik Vejani Dec 12 '15 at 04:57
As you see my quesition , i am using los angeles time zone..If i use curdate it will use server timezone – Vishnu Dec 12 '15 at 05:00
@Vishnu just check this link, may be it will help: https://blog.mozilla.org/it/2012/11/16/converting-timezone-specific-times-in-mysql/ – Pathik Vejani Dec 12 '15 at 05:07

Gouda Elalfy · Answer 2 · 2015-12-12T05:13:54.990

0

You should convert date to timestamp before passing it to mysql:

date_default_timezone_set('America/Los_Angeles'); 
$dt = new DateTime();
$today = $dt->format('Y-m-d'); //outputs 2015-12-07

$ok = outputs("SELECT * FROM `table` WHERE DATE_FORMAT(date,'%Y-%m-%d')= $today" );

edited Dec 12 '15 at 05:13

answered Dec 12 '15 at 04:47

Gouda Elalfy

6,888
1
26
38

not working, I have mentioned that sql clumn value looks like 2015-12-07 22:42:02 – Vishnu Dec 12 '15 at 04:50
You mentioned before that your column timestamp, but if your column like you mentioned you can use NOW() mysql function which return current date time for now. – Gouda Elalfy Dec 12 '15 at 05:04
yes also I mentioned I use custom tiemzone...now() will use server timezone – Vishnu Dec 12 '15 at 05:05

score 0 · Answer 3 · edited May 23 '17 at 12:15

if $today='2015-12-07 22:42:02'; your query will give the result.

$today='2015-12-07 22:42:02';
$ok = mysqli_query($sqli,"SELECT * FROM `table` WHERE `date` = '$today'" );

else do pass the today date and next date and retrieve the value as given

$today='2015-12-07';
$next='2015-12-08';
$ok = mysqli_query($sqli,"SELECT * FROM `table` WHERE `date` >= '$today' and `date` <= '$next'  " );

for more details refer this Oracle SQL : timestamps in where clause How to compare Timestamp in where clause

filter date from timestamp field in mysql

3 Answers3