James
Thanks for writing back. Some feedback:-
Not sure if this is in 4.2, but Phoenix supports casting a numeric to a date.
So when I am referring to a numeric version of a date, I’m referring to a date
that has been formatted as MMDD, and then put in an integer. I am not
talking about a date format where the date is a number of seconds since some
base date. Just want to be clear. The reason is that when I tried the following
…
SELECT CAST(CAST(col * 1000 AS BIGINT) AS DATE) ...
… I did not get expected results. Some examples:-
select 20150101 as DateNumId
,CAST(CAST(20150101 * 1000 AS BIGINT) AS DATE) as Test1
,CAST(CAST(20150101 AS BIGINT) AS DATE) as Test2
from system.catalog
limit 1;
DATENUMID TEST1 TEST2
- -- --
20150101 1970-08-22 1969-12-31
A lot of date function were added in 4.4 that would help you if you need to
extract the day/month/year. Perhaps you can backport them or upgrade?
Unfortunately I don’t think I have this option.
What I can do is cheat though. Given my table will only have records with the
last day of each month in it, this should return the rows I want:-
select *
from mmcallister.TestDateMath
where Product = 'MyProduct'
and DateNumId in (20160331,20160331-10001,20160331-1,20160331-);
(Full test case setup attached)
Regards
Mike
test_phoenix_date_math.sql
Description: test_phoenix_date_math.sql
On Aug 4, 2015, at 3:45 PM, James Taylor jamestay...@apache.org wrote:Not sure if this is in 4.2, but Phoenix supports casting a numeric to a date. You'd need to do this in two steps, though, as we support INTEGER - BIGINT and then BIGINT - DATE. like this:SELECT CAST(CAST(col * 1000 AS BIGINT) AS DATE) ...A lot of date function were added in 4.4 that would help you if you need to extract the day/month/year. Perhaps you can backport them or upgrade?One more built-in that might help you is the TRUNC function which lets you "bucketize" based on HOUR, MINUTE, SECOND, DAY, etc.Thanks,JamesOn Tue, Aug 4, 2015 at 12:14 PM, Michael McAllister mmcallis...@homeaway.com wrote:
HiI have a table with a date stored in it. The date is always the last day of a month. For example, 31-AUG-2015 or 28-FEB-2015. I have the date stored in two separate columns. In one place it is an integer (20150831 and 20150228). In another
place it uses the DATE datatype.I want to write a SQL statement where, given a date (ignore which datatype at the moment), I can return rows for that date, as well as the same date last year, all in one year. The kicker is that we have to interpret the date as a month
end, so we have to factor leap years and February the 29th in.If we didn’t have to deal with the leap year the SQL could be as simple as:-SELECT columnsFROM tableWHERE datenum in (datenum,datenum-1);However, this won’t work for 29-FEB-2016 as (20160229-1 = 20150229)Additionally, we’re on Phoenix 4.2, so we don’t have access to UDFs.So … any ideas how to resolve this query? Is there some built in date math available to me that I can’t find in the documentation online?RegardsMike
