0

This question is more like searching for best solution. I know how to do it in my way. :D

I have few time-intervals as words:

  • today,
  • tomorrow,
  • this_week,
  • this_month;

The problem is that time is saved in the database as UNIX time-stamp.

I will try to show an example. Lets say it's today. That means that I need to select results from database that has been made from start til end of this day.

This means that I need to get current day's first second:

mktime(0, 0, 0);

...last second:

mktime(23, 59, 59);

And query the database with something like this:

WHERE `timestamp` >= first_second OR WHERE `timestamp` <= last_sencond

And yea, I need to write this for all possible strings that are allowed. Are there any neater solution?

Thanks in an advice! :)

daGrevis
  • 21,014
  • 37
  • 100
  • 139

5 Answers5

2

For today you could use:

WHERE CAST(FROM_UNIXTIME(timestamp) AS DATE) = CURDATE()

For tomorrow:

WHERE CAST(FROM_UNIXTIME(timestamp) AS DATE) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)

For this month:

WHERE MONTH(CAST(FROM_UNIXTIME(timestamp) AS DATE)) = MONTH(CURDATE())
Marco
  • 56,740
  • 14
  • 129
  • 152
  • `FROM_UNIXTIME(timestamp)`. Casting an int as a date field won't work - you just get a null value. – Marc B Oct 26 '11 at 14:19
  • @MarcB: I didn't read it was a unix timestamp, my fault. Edited my post. Thanks ;) – Marco Oct 26 '11 at 14:23
  • Still no need for a cast... from_unixtimestamp returns a native datetime anyways. Removed the -1 anyways. – Marc B Oct 26 '11 at 14:24
  • @MarcB: thanks for removing -1. Anyway I used CONVERT to date because I need to compare `CURDATE` with `timestamp` (without time) if I want to check "today"... am I wrong? – Marco Oct 26 '11 at 14:28
  • I could accept this answer if author could show me an example with this week. :( – daGrevis Oct 27 '11 at 12:53
  • I suppose it's: `WHERE WEEK(CAST(FROM_UNIXTIME(timestamp) AS DATE)) = WEEK(CURDATE())`. Thanks, mate! – daGrevis Oct 27 '11 at 12:57
1
where date between $start and $stop

define start and stop depending on the option (day,week,month...etc) with maketime

ka_lin
  • 9,329
  • 6
  • 35
  • 56
1

Well "neater solutions" are subjective right? ... :-) Your requirement seems to be very similar to the question already answered in Mysql: Select all data between two dates

Have a read of all the links there

Community
  • 1
  • 1
Ahmed Masud
  • 21,655
  • 3
  • 33
  • 58
1

Assuming you want records timestamped today:

WHERE date >= UNIX_TIMESTAMP(CURDATE()) AND date <= UNIX_TIMESTAMP(CURDATE()+1)

Note that the second part is not necessary unless you have items timestamped in the future. If you need to get values for other dates, calculate the dates accordingly and convert them to numeric timestamps with UNIX_TIMESTAMP(). This will have better performance than converting the values in the database to date format as that will make using indexes impossible.

Kaivosukeltaja
  • 15,541
  • 4
  • 40
  • 70
  • Is it possible to get results like this also for tomorrow, this week and this month? – daGrevis Oct 26 '11 at 14:24
  • If I understand correctly, to get results for this month I can do like `UNIX_TIMESTAMP(CURDATE() + 60 * 60 * 24 * 30)`? – daGrevis Oct 26 '11 at 14:28
  • That would give you a date over 200 years into the future, because `CURDATE()` returns a date in the format `20111026`. Add 30 to it to get a date 30 days from now, or use some more sophisticated date selectors: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html – Kaivosukeltaja Oct 26 '11 at 14:47
0

I'd recommend storing the DateTime in SQLs native DateTime format and convert to unix time stamps in your application as needed. Of course you may not be able to change the schema, but if you can it would be best to use the most native data type the RDBMS offers. This allows the RDBMS to handle range indexes on DateTime columns more intelligently, for example. Although unix time stamps are usable on any platform using Unix Epoch based math, they're still technically a platform specific format. I wouldn't write a DateTime to SQL as a Windows FILETIME either, despite the fact that the math for FILETIME to DateTime is known.

Jeremiah Gowdy
  • 5,476
  • 3
  • 21
  • 33