phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael McAllister <mmcallis...@homeaway.com>
Subject Re: Date math
Date Tue, 04 Aug 2015 23:12:13 GMT
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 YYYYMMDD, 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-10000,20160331-9999);

(Full test case setup attached)

Regards

Mike
Mime
View raw message