CODEWITHSUNDEEP
phpmysqldatetime
watzon
watzon

Reputation: 2549

Get week from unix timestamp retrieved from database

I have a MySQL database. In a couple of tables, the information that gets stored needs to be retrievable by week. So, I want to be able to do a SELECT FROM *database* WHERE week = *week*. The problem that I have is that the week part is stored as a unix timestamp (to allow for more versatilty like getting the date and time, just time, etc...).

So the question: How can I retrieve this record WHERE date = *date* when the stored date is a unix timestamp and date I'm matching it against is not?

If my question is too confusing and something needs to be rephrased or said in a clearer manner please comment and let me know.

Upvotes: 2

Views: 3703

Answers (1)

Joshua Burns
Joshua Burns

Reputation: 8572

MySQL has a built-in WEEK() method for handling dates: MySQL WEEK() Reference

Unfortunately however, MySQL's WEEK() method only supports DATE datatypes rather than a UNIX TIMESTAMP. Therefore, we must first convert the timestamp to a date so we can then pass that date to the WEEK() method:

SELECT
  *
FROM
  my_table
WHERE
  WEEK(
    DATE_FORMAT(
      FROM_UNIXTIME('my_unix_timestamp_col'),
      '%e %b %Y'
    )
  ) = 51

If you have a column which is the DATE data-type, the query can be simplified (and can also use indexes):

SELECT * FROM my_table WHERE WEEK(my_date_col) = 51

Upvotes: 2

Related Questions