Re: Date math

Tue, 04 Aug 2015 16:12:51 -0700 

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

Attachment: test_phoenix_date_math.sql
Description: test_phoenix_date_math.sql


On Aug 4, 2015, at 3:45 PM, James Taylor <[email protected]> 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,
James

On Tue, Aug 4, 2015 at 12:14 PM, Michael McAllister <[email protected]> wrote:

Hi

 

I 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 <columns>

FROM <table>

WHERE datenum in (<datenum>,<datenum>-10000);

 

However, this won’t work for 29-FEB-2016 as (20160229-10000 = 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?

 

Regards

 

Mike

 



Reply via email to