1

I have events stored in the MySQL datebase with start date and time stored in column 'startTimeStamp' in UNIX format.

When I try to get events for particular date using simple query

SELECT * FROM events WHERE FROM_UNIXTIME(startTimeStamp, '%Y-%m-%d') = '2013-08-20'

everything works fine when date and time under timestamp is bigger then 2AM (i.e. 2013-08-20 03:00:00) but when it's earlier it's not displaying in same day.

Quick example: I have two events - same date but different time

  1. 2013-08-20 03:00:00 (1376960400)
  2. 2013-08-20 00:00:00 (1376949600)

I will get the first one when I run query mentioned before, but not the second one. To get the second one I have to query for events from day earlier (2013-08-19)

Do you have any idea why? Maybe I'm missing something?

Thanks a lot!

Real299
  • 11
  • 1

4 Answers4

0

See what you timezone is:

SELECT @@global.time_zone, @@session.time_zone;

And then set it correctly:

SET time_zone = timezonename;

EDIT: This might help:MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

Community
  • 1
  • 1
Mihai
  • 26,325
  • 7
  • 66
  • 81
0

I think you might have chosen a place where the daylight saving time applies to. The time shifting in this case is mostly done between 2AM and 3AM in the morning, which may result in the different days.

Michael
  • 2,309
  • 1
  • 23
  • 34
0

It's basically because the Unix timestamp meaning. It's just the number of seconds from 1-1-1970, so technically the 1376949600 value could still be the previous day, depends on your timezone.

In my case:

echo date("d-m-Y h:i", 1376949600);
// output: 19-08-2013 11:00
juanra
  • 1,602
  • 19
  • 17
0

Use like this:

$d="1376960400";
echo date("Y-m-d",$d);

you can use which format you want from this link

Moataz Mohamed
  • 26
  • 3