phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Date math
Date Tue, 04 Aug 2015 20:45:30 GMT
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 <
mmcallister@homeaway.com> 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
>
>
>

Mime
View raw message