Chris Edwards Chris Edwards - 4 months ago 10
SQL Question

Looking for mysql rows between certain unix time stamps

I am trying to write a mysql query that pulls in a unix timestamp converts it to %Y%m%d format then finds the rows between a certain date range. The query works until we get the WHERE clause. I have no idea why it isn't working. I was hoping some sql guru out here could help me.

Thanks,

Chris

SELECT
date_format(from_unixtime('timestamp'),'%Y%m%d') AS 'datecreated'
,`order_id`
, `email`
, `subtotal`
, `shipping_cost`
, `total`
, `status`
FROM
`orders`
WHERE 'datecreated' >= '20160501' AND 'datecreated' < '20160512'

Answer

You CANT use the alias on the where

Either you write the date_format function again

SELECT 
        date_format(from_unixtime('timestamp'),'%Y%m%d') AS `datecreated`
        ,`order_id`
        , `email`
        , `subtotal`
        , `shipping_cost`
        , `total`
        , `status`
    FROM
        `orders`
    WHERE date_format(from_unixtime('timestamp'),'%Y%m%d') >= '20160501' 
     AND  date_format(from_unixtime('timestamp'),'%Y%m%d') < '20160512'

Or create a sub query

SELECT *
FROM (
    SELECT 
        date_format(from_unixtime('timestamp'),'%Y%m%d') AS `datecreated`
        ,`order_id`
        , `email`
        , `subtotal`
        , `shipping_cost`
        , `total`
        , `status`
    FROM
        `orders`
     ) T
 WHERE `datecreated` >= '20160501' AND 'datecreated' < '20160512'

EDIT:

also fieldname use back thicks not single quotes

  `datecreated` field name
  'datecreated' string constant
Comments