Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
  /*
   * Licensed under the Apache License, Version 2.0 (the "License");
   * you may not use this file except in compliance with the License.
   * You may obtain a copy of the License at
   *
   *     http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  * See the License for the specific language governing permissions and
  * limitations under the License.
  */
 package com.facebook.presto.operator.scalar;
 
 
 
 import static com.facebook.presto.spi.type.TimeZoneKey.getTimeZoneKey;
 import static com.facebook.presto.spi.type.TimeZoneKey.getTimeZoneKeyForOffset;
 import static com.facebook.presto.util.DateTimeZoneIndex.getDateTimeZone;
 import static java.util.Locale.ENGLISH;
 import static org.joda.time.Days.daysBetween;
 import static org.joda.time.Hours.hoursBetween;
 import static org.joda.time.Minutes.minutesBetween;
 import static org.joda.time.Months.monthsBetween;
 import static org.joda.time.Seconds.secondsBetween;
 import static org.joda.time.Weeks.weeksBetween;
 import static org.joda.time.Years.yearsBetween;
 
 public class TestDateTimeFunctions
 {
     private static final TimeZoneKey TIME_ZONE_KEY = getTimeZoneKey("Asia/Kathmandu");
     private static final DateTimeZone DATE_TIME_ZONE = getDateTimeZone();
     private static final TimeZoneKey WEIRD_ZONE_KEY = getTimeZoneKey("+07:09");
     private static final DateTimeZone WEIRD_ZONE = getDateTimeZone();
 
     private static final DateTime DATE = new DateTime(2001, 8, 22, 0, 0, 0, 0, .);
     private static final String DATE_LITERAL = "DATE '2001-08-22'";
 
     private static final DateTime TIME = new DateTime(1970, 1, 1, 3, 4, 5, 321, );
     private static final String TIME_LITERAL = "TIME '03:04:05.321'";
     private static final DateTime WEIRD_TIME = new DateTime(1970, 1, 1, 3, 4, 5, 321, );
     private static final String WEIRD_TIME_LITERAL = "TIME '03:04:05.321 +07:09'";
 
     private static final DateTime TIMESTAMP = new DateTime(2001, 8, 22, 3, 4, 5, 321, );
     private static final String TIMESTAMP_LITERAL = "TIMESTAMP '2001-08-22 03:04:05.321'";
     private static final DateTime WEIRD_TIMESTAMP = new DateTime(2001, 8, 22, 3, 4, 5, 321, );
     private static final String WEIRD_TIMESTAMP_LITERAL = "TIMESTAMP '2001-08-22 03:04:05.321 +07:09'";
 
     private static final TimeZoneKey WEIRD_TIME_ZONE_KEY = getTimeZoneKeyForOffset(7 * 60 + 9);
     private Session session;
 
     @BeforeClass
     public void setUp()
     {
          = Session.builder()
                 .setUser("user")
                 .setSource("test")
                 .setCatalog("catalog")
                 .setSchema("schema")
                 .setTimeZoneKey()
                 .setLocale()
                 .build();
          = new FunctionAssertions();
     }
 
     @Test
     public void testCurrentDate()
             throws Exception
     {
         // current date is the time at midnight in the session time zone
         DateMidnight dateMidnight = new DateMidnight(.getStartTime(), );
         int days = (int..toDays(dateMidnight.getMillis());
         assertFunction("CURRENT_DATE"new SqlDate(days));
     }
 
     @Test
     public void testLocalTime()
             throws Exception
     {
         long millis = new LocalTime(.getStartTime(), ).getMillisOfDay();
        .assertFunction("LOCALTIME"toTime(millis));
    }
    @Test
    public void testCurrentTime()
            throws Exception
    {
        long millis = new LocalTime(.getStartTime(), ).getMillisOfDay();
        .assertFunction("CURRENT_TIME"new SqlTimeWithTimeZone(millis.getTimeZoneKey()));
    }
    @Test
    public void testLocalTimestamp()
    {
        .assertFunction("localtimestamp"toTimestamp(.getStartTime()));
    }
    @Test
    public void testCurrentTimestamp()
    {
    }
    @Test
    public void testFromUnixTime()
    {
        DateTime dateTime = new DateTime(2001, 1, 22, 3, 4, 5, 0, );
        double seconds = dateTime.getMillis() / 1000.0;
        assertFunction("from_unixtime(" + seconds + ")"toTimestamp(dateTime));
        dateTime = new DateTime(2001, 1, 22, 3, 4, 5, 888, );
        seconds = dateTime.getMillis() / 1000.0;
        assertFunction("from_unixtime(" + seconds + ")"toTimestamp(dateTime));
    }
    @Test
    public void testToUnixTime()
    {
        assertFunction("to_unixtime(" +  + ")".getMillis() / 1000.0);
        assertFunction("to_unixtime(" +  + ")".getMillis() / 1000.0);
    }
    @Test
    public void testTimeZone()
    {
        assertFunction("hour(" +  + ")".getHourOfDay());
        assertFunction("minute(" +  + ")".getMinuteOfHour());
        assertFunction("hour(" +  + ")".getHourOfDay());
        assertFunction("minute(" +  + ")".getMinuteOfHour());
        assertFunction("current_timezone()".getId());
    }
    @Test
    public void testAtTimeZone()
    {
        .assertFunction("current_timestamp at time zone interval '07:09' hour to minute",
                new SqlTimestampWithTimeZone(.getStartTime(), ));
        .assertFunction("current_timestamp at time zone 'Asia/Oral'"new SqlTimestampWithTimeZone(.getStartTime(), TimeZone.getTimeZone("Asia/Oral")));
        .assertFunction("now() at time zone 'Asia/Oral'"new SqlTimestampWithTimeZone(.getStartTime(), TimeZone.getTimeZone("Asia/Oral")));
        .assertFunction("current_timestamp at time zone '+07:09'"new SqlTimestampWithTimeZone(.getStartTime(), ));
    }
    @Test
    public void testPartFunctions()
    {
        assertFunction("second(" +  + ")".getSecondOfMinute());
        assertFunction("minute(" +  + ")".getMinuteOfHour());
        assertFunction("hour(" +  + ")".getHourOfDay());
        assertFunction("day_of_week(" +  + ")".dayOfWeek().get());
        assertFunction("dow(" +  + ")".dayOfWeek().get());
        assertFunction("day(" +  + ")".getDayOfMonth());
        assertFunction("day_of_month(" +  + ")".getDayOfMonth());
        assertFunction("day_of_year(" +  + ")".dayOfYear().get());
        assertFunction("doy(" +  + ")".dayOfYear().get());
        assertFunction("week(" +  + ")".weekOfWeekyear().get());
        assertFunction("week_of_year(" +  + ")".weekOfWeekyear().get());
        assertFunction("month(" +  + ")".getMonthOfYear());
        assertFunction("quarter(" +  + ")".getMonthOfYear() / 4 + 1);
        assertFunction("year(" +  + ")".getYear());
        assertFunction("timezone_hour(" +  + ")", 5);
        assertFunction("timezone_hour(localtimestamp)", 5);
        assertFunction("timezone_hour(current_timestamp)", 5);
        assertFunction("minute(" +  + ")".getMinuteOfHour());
        assertFunction("hour(" +  + ")".getHourOfDay());
        assertFunction("day_of_week(" +  + ")".dayOfWeek().get());
        assertFunction("dow(" +  + ")".dayOfWeek().get());
        assertFunction("day_of_month(" +  + ")".getDayOfMonth());
        assertFunction("day_of_year(" +  + ")".dayOfYear().get());
        assertFunction("doy(" +  + ")".dayOfYear().get());
        assertFunction("week(" +  + ")".weekOfWeekyear().get());
        assertFunction("week_of_year(" +  + ")".weekOfWeekyear().get());
        assertFunction("month(" +  + ")".getMonthOfYear());
        assertFunction("quarter(" +  + ")".getMonthOfYear() / 4 + 1);
        assertFunction("year(" +  + ")".getYear());
        assertFunction("timezone_minute(" +  + ")", 9);
        assertFunction("timezone_hour(" +  + ")", 7);
    }
    @Test
    public void testYearOfWeek()
    {
        assertFunction("year_of_week(DATE '2001-08-22')", 2001);
        assertFunction("yow(DATE '2001-08-22')", 2001);
        assertFunction("year_of_week(DATE '2005-01-02')", 2004);
        assertFunction("year_of_week(DATE '2008-12-28')", 2008);
        assertFunction("year_of_week(DATE '2008-12-29')", 2009);
        assertFunction("year_of_week(DATE '2009-12-31')", 2009);
        assertFunction("year_of_week(DATE '2010-01-03')", 2009);
        assertFunction("year_of_week(TIMESTAMP '2001-08-22 03:04:05.321 +07:09')", 2001);
        assertFunction("year_of_week(TIMESTAMP '2010-01-03 03:04:05.321')", 2009);
    }
    @Test
    public void testExtractFromTimestamp()
    {
        assertFunction("extract(second FROM " +  + ")".getSecondOfMinute());
        assertFunction("extract(minute FROM " +  + ")".getMinuteOfHour());
        assertFunction("extract(hour FROM " +  + ")".getHourOfDay());
        assertFunction("extract(day_of_week FROM " +  + ")".getDayOfWeek());
        assertFunction("extract(dow FROM " +  + ")".getDayOfWeek());
        assertFunction("extract(day FROM " +  + ")".getDayOfMonth());
        assertFunction("extract(day_of_month FROM " +  + ")".getDayOfMonth());
        assertFunction("extract(day_of_year FROM " +  + ")".getDayOfYear());
        assertFunction("extract(year_of_week FROM " +  + ")", 2001);
        assertFunction("extract(doy FROM " +  + ")".getDayOfYear());
        assertFunction("extract(week FROM " +  + ")".getWeekOfWeekyear());
        assertFunction("extract(month FROM " +  + ")".getMonthOfYear());
        assertFunction("extract(quarter FROM " +  + ")".getMonthOfYear() / 4 + 1);
        assertFunction("extract(year FROM " +  + ")".getYear());
        assertFunction("extract(second FROM " +  + ")".getSecondOfMinute());
        assertFunction("extract(minute FROM " +  + ")".getMinuteOfHour());
        assertFunction("extract(hour FROM " +  + ")".getHourOfDay());
        assertFunction("extract(day_of_week FROM " +  + ")".getDayOfWeek());
        assertFunction("extract(dow FROM " +  + ")".getDayOfWeek());
        assertFunction("extract(day FROM " +  + ")".getDayOfMonth());
        assertFunction("extract(day_of_month FROM " +  + ")".getDayOfMonth());
        assertFunction("extract(day_of_year FROM " +  + ")".getDayOfYear());
        assertFunction("extract(doy FROM " +  + ")".getDayOfYear());
        assertFunction("extract(week FROM " +  + ")".getWeekOfWeekyear());
        assertFunction("extract(month FROM " +  + ")".getMonthOfYear());
        assertFunction("extract(quarter FROM " +  + ")".getMonthOfYear() / 4 + 1);
        assertFunction("extract(year FROM " +  + ")".getYear());
        assertFunction("extract(timezone_minute FROM " +  + ")", 9);
        assertFunction("extract(timezone_hour FROM " +  + ")", 7);
    }
    @Test
    public void testExtractFromTime()
    {
        assertFunction("extract(second FROM " +  + ")", 5);
        assertFunction("extract(minute FROM " +  + ")", 4);
        assertFunction("extract(hour FROM " +  + ")", 3);
        assertFunction("extract(second FROM " +  + ")", 5);
        assertFunction("extract(minute FROM " +  + ")", 4);
        assertFunction("extract(hour FROM " +  + ")", 3);
    }
    @Test
    public void testExtractFromDate()
    {
        assertFunction("extract(day_of_week FROM " +  + ")", 3);
        assertFunction("extract(dow FROM " +  + ")", 3);
        assertFunction("extract(day FROM " +  + ")", 22);
        assertFunction("extract(day_of_month FROM " +  + ")", 22);
        assertFunction("extract(day_of_year FROM " +  + ")", 234);
        assertFunction("extract(doy FROM " +  + ")", 234);
        assertFunction("extract(year_of_week FROM " +  + ")", 2001);
        assertFunction("extract(yow FROM " +  + ")", 2001);
        assertFunction("extract(week FROM " +  + ")", 34);
        assertFunction("extract(month FROM " +  + ")", 8);
        assertFunction("extract(quarter FROM " +  + ")", 3);
        assertFunction("extract(year FROM " +  + ")", 2001);
        assertFunction("extract(quarter FROM DATE '2001-01-01')", 1);
        assertFunction("extract(quarter FROM DATE '2001-03-31')", 1);
        assertFunction("extract(quarter FROM DATE '2001-04-01')", 2);
        assertFunction("extract(quarter FROM DATE '2001-06-30')", 2);
        assertFunction("extract(quarter FROM DATE '2001-07-01')", 3);
        assertFunction("extract(quarter FROM DATE '2001-09-30')", 3);
        assertFunction("extract(quarter FROM DATE '2001-10-01')", 4);
        assertFunction("extract(quarter FROM DATE '2001-12-31')", 4);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-01-01 00:00:00.000')", 1);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-03-31 23:59:59.999')", 1);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-04-01 00:00:00.000')", 2);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-06-30 23:59:59.999')", 2);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-07-01 00:00:00.000')", 3);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-09-30 23:59:59.999')", 3);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-10-01 00:00:00.000')", 4);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-12-31 23:59:59.999')", 4);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-01-01 00:00:00.000 +06:00')", 1);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-03-31 23:59:59.999 +06:00')", 1);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-04-01 00:00:00.000 +06:00')", 2);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-06-30 23:59:59.999 +06:00')", 2);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-07-01 00:00:00.000 +06:00')", 3);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-09-30 23:59:59.999 +06:00')", 3);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-10-01 00:00:00.000 +06:00')", 4);
        assertFunction("extract(quarter FROM TIMESTAMP '2001-12-31 23:59:59.999 +06:00')", 4);
    }
    @Test
    public void testExtractFromInterval()
    {
        assertFunction("extract(second FROM INTERVAL '5' SECOND)", 5);
        assertFunction("extract(second FROM INTERVAL '65' SECOND)", 5);
        assertFunction("extract(minute FROM INTERVAL '4' MINUTE)", 4);
        assertFunction("extract(minute FROM INTERVAL '64' MINUTE)", 4);
        assertFunction("extract(minute FROM INTERVAL '247' SECOND)", 4);
        assertFunction("extract(hour FROM INTERVAL '3' HOUR)", 3);
        assertFunction("extract(hour FROM INTERVAL '27' HOUR)", 3);
        assertFunction("extract(hour FROM INTERVAL '187' MINUTE)", 3);
        assertFunction("extract(day FROM INTERVAL '2' DAY)", 2);
        assertFunction("extract(day FROM INTERVAL '55' HOUR)", 2);
        assertFunction("extract(month FROM INTERVAL '3' MONTH)", 3);
        assertFunction("extract(month FROM INTERVAL '15' MONTH)", 3);
        assertFunction("extract(year FROM INTERVAL '2' YEAR)", 2);
        assertFunction("extract(year FROM INTERVAL '29' MONTH)", 2);
    }
    @Test
    public void testTruncateTimestamp()
    {
        DateTime result = ;
        result = result.withMillisOfSecond(0);
        assertFunction("date_trunc('second', " +  + ")"toTimestamp(result));
        result = result.withSecondOfMinute(0);
        assertFunction("date_trunc('minute', " +  + ")"toTimestamp(result));
        result = result.withMinuteOfHour(0);
        assertFunction("date_trunc('hour', " +  + ")"toTimestamp(result));
        result = result.withHourOfDay(0);
        assertFunction("date_trunc('day', " +  + ")"toTimestamp(result));
        result = result.withDayOfMonth(20);
        assertFunction("date_trunc('week', " +  + ")"toTimestamp(result));
        result = result.withDayOfMonth(1);
        assertFunction("date_trunc('month', " +  + ")"toTimestamp(result));
        result = result.withMonthOfYear(7);
        assertFunction("date_trunc('quarter', " +  + ")"toTimestamp(result));
        result = result.withMonthOfYear(1);
        assertFunction("date_trunc('year', " +  + ")"toTimestamp(result));
        result = ;
        result = result.withMillisOfSecond(0);
        assertFunction("date_trunc('second', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withSecondOfMinute(0);
        assertFunction("date_trunc('minute', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withMinuteOfHour(0);
        assertFunction("date_trunc('hour', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withHourOfDay(0);
        assertFunction("date_trunc('day', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withDayOfMonth(20);
        assertFunction("date_trunc('week', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withDayOfMonth(1);
        assertFunction("date_trunc('month', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withMonthOfYear(7);
        assertFunction("date_trunc('quarter', " +  + ")"toTimestampWithTimeZone(result));
        result = result.withMonthOfYear(1);
        assertFunction("date_trunc('year', " +  + ")"toTimestampWithTimeZone(result));
    }
    @Test
    public void testTruncateTime()
    {
        DateTime result = ;
        result = result.withMillisOfSecond(0);
        assertFunction("date_trunc('second', " +  + ")"toTime(result));
        result = result.withSecondOfMinute(0);
        assertFunction("date_trunc('minute', " +  + ")"toTime(result));
        result = result.withMinuteOfHour(0);
        assertFunction("date_trunc('hour', " +  + ")"toTime(result));
        result = ;
        result = result.withMillisOfSecond(0);
        assertFunction("date_trunc('second', " +  + ")"toTimeWithTimeZone(result));
        result = result.withSecondOfMinute(0);
        assertFunction("date_trunc('minute', " +  + ")"toTimeWithTimeZone(result));
        result = result.withMinuteOfHour(0);
        assertFunction("date_trunc('hour', " +  + ")"toTimeWithTimeZone(result));
    }
    @Test
    public void testTruncateDate()
    {
        DateTime result = ;
        assertFunction("date_trunc('day', " +  + ")"toDate(result));
        result = result.withDayOfMonth(20);
        assertFunction("date_trunc('week', " +  + ")"toDate(result));
        result = result.withDayOfMonth(1);
        assertFunction("date_trunc('month', " +  + ")"toDate(result));
        result = result.withMonthOfYear(7);
        assertFunction("date_trunc('quarter', " +  + ")"toDate(result));
        result = result.withMonthOfYear(1);
        assertFunction("date_trunc('year', " +  + ")"toDate(result));
    }
    @Test
    public void testAddFieldToTimestamp()
    {
        assertFunction("date_add('second', 3, " +  + ")"toTimestamp(.plusSeconds(3)));
        assertFunction("date_add('minute', 3, " +  + ")"toTimestamp(.plusMinutes(3)));
        assertFunction("date_add('hour', 3, " +  + ")"toTimestamp(.plusHours(3)));
        assertFunction("date_add('day', 3, " +  + ")"toTimestamp(.plusDays(3)));
        assertFunction("date_add('week', 3, " +  + ")"toTimestamp(.plusWeeks(3)));
        assertFunction("date_add('month', 3, " +  + ")"toTimestamp(.plusMonths(3)));
        assertFunction("date_add('quarter', 3, " +  + ")"toTimestamp(.plusMonths(3 * 3)));
        assertFunction("date_add('year', 3, " +  + ")"toTimestamp(.plusYears(3)));
        assertFunction("date_add('second', 3, " +  + ")"toTimestampWithTimeZone(.plusSeconds(3)));
        assertFunction("date_add('minute', 3, " +  + ")"toTimestampWithTimeZone(.plusMinutes(3)));
        assertFunction("date_add('hour', 3, " +  + ")"toTimestampWithTimeZone(.plusHours(3)));
        assertFunction("date_add('day', 3, " +  + ")"toTimestampWithTimeZone(.plusDays(3)));
        assertFunction("date_add('week', 3, " +  + ")"toTimestampWithTimeZone(.plusWeeks(3)));
        assertFunction("date_add('month', 3, " +  + ")"toTimestampWithTimeZone(.plusMonths(3)));
        assertFunction("date_add('quarter', 3, " +  + ")"toTimestampWithTimeZone(.plusMonths(3 * 3)));
        assertFunction("date_add('year', 3, " +  + ")"toTimestampWithTimeZone(.plusYears(3)));
    }
    @Test
    public void testAddFieldToDate()
    {
        assertFunction("date_add('day', 3, " +  + ")"toDate(.plusDays(3)));
        assertFunction("date_add('week', 3, " +  + ")"toDate(.plusWeeks(3)));
        assertFunction("date_add('month', 3, " +  + ")"toDate(.plusMonths(3)));
        assertFunction("date_add('quarter', 3, " +  + ")"toDate(.plusMonths(3 * 3)));
        assertFunction("date_add('year', 3, " +  + ")"toDate(.plusYears(3)));
    }
    @Test
    public void testAddFieldToTime()
    {
        assertFunction("date_add('second', 3, " +  + ")"toTime(.plusSeconds(3)));
        assertFunction("date_add('minute', 3, " +  + ")"toTime(.plusMinutes(3)));
        assertFunction("date_add('hour', 3, " +  + ")"toTime(.plusHours(3)));
        assertFunction("date_add('second', 3, " +  + ")"toTimeWithTimeZone(.plusSeconds(3)));
        assertFunction("date_add('minute', 3, " +  + ")"toTimeWithTimeZone(.plusMinutes(3)));
        assertFunction("date_add('hour', 3, " +  + ")"toTimeWithTimeZone(.plusHours(3)));
    }
    @Test
    public void testDateDiffTimestamp()
    {
        DateTime baseDateTime = new DateTime(1960, 5, 3, 7, 2, 9, 678, );
        String baseDateTimeLiteral = "TIMESTAMP '1960-05-03 07:02:09.678'";
        assertFunction("date_diff('second', " + baseDateTimeLiteral + ", " +  + ")"secondsBetween(baseDateTime).getSeconds());
        assertFunction("date_diff('minute', " + baseDateTimeLiteral + ", " +  + ")"minutesBetween(baseDateTime).getMinutes());
        assertFunction("date_diff('hour', " + baseDateTimeLiteral + ", " +  + ")"hoursBetween(baseDateTime).getHours());
        assertFunction("date_diff('day', " + baseDateTimeLiteral + ", " +  + ")"daysBetween(baseDateTime).getDays());
        assertFunction("date_diff('week', " + baseDateTimeLiteral + ", " +  + ")"weeksBetween(baseDateTime).getWeeks());
        assertFunction("date_diff('month', " + baseDateTimeLiteral + ", " +  + ")"monthsBetween(baseDateTime).getMonths());
        assertFunction("date_diff('quarter', " + baseDateTimeLiteral + ", " +  + ")"monthsBetween(baseDateTime).getMonths() / 3);
        assertFunction("date_diff('year', " + baseDateTimeLiteral + ", " +  + ")"yearsBetween(baseDateTime).getYears());
        DateTime weirdBaseDateTime = new DateTime(1960, 5, 3, 7, 2, 9, 678, );
        String weirdBaseDateTimeLiteral = "TIMESTAMP '1960-05-03 07:02:09.678 +07:09'";
        assertFunction("date_diff('second', " + weirdBaseDateTimeLiteral + ", " +  + ")"secondsBetween(weirdBaseDateTime).getSeconds());
        assertFunction("date_diff('minute', " + weirdBaseDateTimeLiteral + ", " +  + ")"minutesBetween(weirdBaseDateTime).getMinutes());
        assertFunction("date_diff('hour', " + weirdBaseDateTimeLiteral + ", " +  + ")"hoursBetween(weirdBaseDateTime).getHours());
        assertFunction("date_diff('day', " + weirdBaseDateTimeLiteral + ", " +  + ")"daysBetween(weirdBaseDateTime).getDays());
        assertFunction("date_diff('week', " + weirdBaseDateTimeLiteral + ", " +  + ")"weeksBetween(weirdBaseDateTime).getWeeks());
        assertFunction("date_diff('month', " + weirdBaseDateTimeLiteral + ", " +  + ")"monthsBetween(weirdBaseDateTime).getMonths());
        assertFunction("date_diff('quarter', " + weirdBaseDateTimeLiteral + ", " +  + ")",
                monthsBetween(weirdBaseDateTime).getMonths() / 3);
        assertFunction("date_diff('year', " + weirdBaseDateTimeLiteral + ", " +  + ")"yearsBetween(weirdBaseDateTime).getYears());
    }
    @Test
    public void testDateDiffDate()
    {
        DateTime baseDateTime = new DateTime(1960, 5, 3, 0, 0, 0, 0, .);
        String baseDateTimeLiteral = "DATE '1960-05-03'";
        assertFunction("date_diff('day', " + baseDateTimeLiteral + ", " +  + ")"daysBetween(baseDateTime).getDays());
        assertFunction("date_diff('week', " + baseDateTimeLiteral + ", " +  + ")"weeksBetween(baseDateTime).getWeeks());
        assertFunction("date_diff('month', " + baseDateTimeLiteral + ", " +  + ")"monthsBetween(baseDateTime).getMonths());
        assertFunction("date_diff('quarter', " + baseDateTimeLiteral + ", " +  + ")"monthsBetween(baseDateTime).getMonths() / 3);
        assertFunction("date_diff('year', " + baseDateTimeLiteral + ", " +  + ")"yearsBetween(baseDateTime).getYears());
    }
    @Test
    public void testDateDiffTime()
    {
        DateTime baseDateTime = new DateTime(1970, 1, 1, 7, 2, 9, 678, );
        String baseDateTimeLiteral = "TIME '07:02:09.678'";
        assertFunction("date_diff('second', " + baseDateTimeLiteral + ", " +  + ")"secondsBetween(baseDateTime).getSeconds());
        assertFunction("date_diff('minute', " + baseDateTimeLiteral + ", " +  + ")"minutesBetween(baseDateTime).getMinutes());
        assertFunction("date_diff('hour', " + baseDateTimeLiteral + ", " +  + ")"hoursBetween(baseDateTime).getHours());
        DateTime weirdBaseDateTime = new DateTime(1970, 1, 1, 7, 2, 9, 678, );
        String weirdBaseDateTimeLiteral = "TIME '07:02:09.678 +07:09'";
        assertFunction("date_diff('second', " + weirdBaseDateTimeLiteral + ", " +  + ")"secondsBetween(weirdBaseDateTime).getSeconds());
        assertFunction("date_diff('minute', " + weirdBaseDateTimeLiteral + ", " +  + ")"minutesBetween(weirdBaseDateTime).getMinutes());
        assertFunction("date_diff('hour', " + weirdBaseDateTimeLiteral + ", " +  + ")"hoursBetween(weirdBaseDateTime).getHours());
    }
    @Test
    public void testParseDatetime()
    {
        assertFunction("parse_datetime('1960/01/22 03:04', 'YYYY/MM/DD HH:mm')"toTimestampWithTimeZone(new DateTime(1960, 1, 22, 3, 4, 0, 0, )));
        assertFunction("parse_datetime('1960/01/22 03:04 Asia/Oral', 'YYYY/MM/DD HH:mm ZZZZZ')",
                toTimestampWithTimeZone(new DateTime(1960, 1, 22, 3, 4, 0, 0, DateTimeZone.forID("Asia/Oral"))));
        assertFunction("parse_datetime('1960/01/22 03:04 +0500', 'YYYY/MM/DD HH:mm Z')",
                toTimestampWithTimeZone(new DateTime(1960, 1, 22, 3, 4, 0, 0, DateTimeZone.forOffsetHours(5))));
    }
    @Test(expectedExceptions = PrestoException.class, expectedExceptionsMessageRegExp = "Both printing and parsing not supported")
    public void testInvalidDateParseFormat()
    {
        assertFunction("date_parse('%Y-%M-%d', '')", 0);
    }
    @Test
    public void testFormatDatetime()
    {
        assertFunction("format_datetime(" +  + ", 'YYYY/MM/dd HH:mm')""2001/08/22 03:04");
        assertFunction("format_datetime(" +  + ", 'YYYY/MM/dd HH:mm ZZZZ')""2001/08/22 03:04 Asia/Kathmandu");
        assertFunction("format_datetime(" +  + ", 'YYYY/MM/dd HH:mm')""2001/08/22 03:04");
        assertFunction("format_datetime(" +  + ", 'YYYY/MM/dd HH:mm ZZZZ')""2001/08/22 03:04 +07:09");
    }
    @Test
    public void testDateFormat()
    {
        String dateTimeLiteral = "TIMESTAMP '2001-01-09 13:04:05.321'";
        assertFunction("date_format(" + dateTimeLiteral + ", '%a')""Tue");
        assertFunction("date_format(" + dateTimeLiteral + ", '%b')""Jan");
        assertFunction("date_format(" + dateTimeLiteral + ", '%c')""1");
        assertFunction("date_format(" + dateTimeLiteral + ", '%d')""09");
        assertFunction("date_format(" + dateTimeLiteral + ", '%e')""9");
        assertFunction("date_format(" + dateTimeLiteral + ", '%f')""000321");
        assertFunction("date_format(" + dateTimeLiteral + ", '%H')""13");
        assertFunction("date_format(" + dateTimeLiteral + ", '%h')""01");
        assertFunction("date_format(" + dateTimeLiteral + ", '%I')""01");
        assertFunction("date_format(" + dateTimeLiteral + ", '%i')""04");
        assertFunction("date_format(" + dateTimeLiteral + ", '%j')""009");
        assertFunction("date_format(" + dateTimeLiteral + ", '%k')""13");
        assertFunction("date_format(" + dateTimeLiteral + ", '%l')""1");
        assertFunction("date_format(" + dateTimeLiteral + ", '%M')""January");
        assertFunction("date_format(" + dateTimeLiteral + ", '%m')""01");
        assertFunction("date_format(" + dateTimeLiteral + ", '%p')""PM");
        assertFunction("date_format(" + dateTimeLiteral + ", '%r')""01:04:05 PM");
        assertFunction("date_format(" + dateTimeLiteral + ", '%S')""05");
        assertFunction("date_format(" + dateTimeLiteral + ", '%s')""05");
        assertFunction("date_format(" + dateTimeLiteral + ", '%T')""13:04:05");
        assertFunction("date_format(" + dateTimeLiteral + ", '%v')""02");
        assertFunction("date_format(" + dateTimeLiteral + ", '%W')""Tuesday");
        assertFunction("date_format(" + dateTimeLiteral + ", '%w')""2");
        assertFunction("date_format(" + dateTimeLiteral + ", '%Y')""2001");
        assertFunction("date_format(" + dateTimeLiteral + ", '%y')""01");
        assertFunction("date_format(" + dateTimeLiteral + ", '%%')""%");
        assertFunction("date_format(" + dateTimeLiteral + ", 'foo')""foo");
        assertFunction("date_format(" + dateTimeLiteral + ", '%g')""g");
        assertFunction("date_format(" + dateTimeLiteral + ", '%4')""4");
        assertFunction("date_format(" + dateTimeLiteral + ", '%x %v')""2001 02");
        String wierdDateTimeLiteral = "TIMESTAMP '2001-01-09 13:04:05.321 +07:09'";
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%a')""Tue");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%b')""Jan");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%c')""1");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%d')""09");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%e')""9");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%f')""000321");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%H')""13");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%h')""01");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%I')""01");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%i')""04");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%j')""009");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%k')""13");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%l')""1");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%M')""January");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%m')""01");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%p')""PM");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%r')""01:04:05 PM");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%S')""05");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%s')""05");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%T')""13:04:05");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%v')""02");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%W')""Tuesday");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%w')""2");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%Y')""2001");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%y')""01");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%%')""%");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", 'foo')""foo");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%g')""g");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%4')""4");
        assertFunction("date_format(" + wierdDateTimeLiteral + ", '%x %v')""2001 02");
    }
    @Test
    public void testDateParse()
    {
        assertFunction("date_parse('2013', '%Y')"toTimestamp(new DateTime(2013, 1, 1, 0, 0, 0, 0, )));
        assertFunction("date_parse('2013-05', '%Y-%m')"toTimestamp(new DateTime(2013, 5, 1, 0, 0, 0, 0, )));
        assertFunction("date_parse('2013-05-17', '%Y-%m-%d')"toTimestamp(new DateTime(2013, 5, 17, 0, 0, 0, 0, )));
        assertFunction("date_parse('2013-05-17 12:35:10', '%Y-%m-%d %h:%i:%s')"toTimestamp(new DateTime(2013, 5, 17, 0, 35, 10, 0, )));
        assertFunction("date_parse('2013-05-17 12:35:10 PM', '%Y-%m-%d %h:%i:%s %p')"toTimestamp(new DateTime(2013, 5, 17, 12, 35, 10, 0, )));
        assertFunction("date_parse('2013-05-17 12:35:10 AM', '%Y-%m-%d %h:%i:%s %p')"toTimestamp(new DateTime(2013, 5, 17, 0, 35, 10, 0, )));
        assertFunction("date_parse('2013-05-17 00:35:10', '%Y-%m-%d %H:%i:%s')"toTimestamp(new DateTime(2013, 5, 17, 0, 35, 10, 0, )));
        assertFunction("date_parse('2013-05-17 23:35:10', '%Y-%m-%d %H:%i:%s')"toTimestamp(new DateTime(2013, 5, 17, 23, 35, 10, 0, )));
        assertFunction("date_parse('abc 2013-05-17 fff 23:35:10 xyz', 'abc %Y-%m-%d fff %H:%i:%s xyz')"toTimestamp(new DateTime(2013, 5, 17, 23, 35, 10, 0, )));
        assertFunction("date_parse('2013 14', '%Y %y')"toTimestamp(new DateTime(2014, 1, 1, 0, 0, 0, 0, )));
        assertFunction("date_parse('1998 53', '%x %v')"toTimestamp(new DateTime(1998, 12, 28, 0, 0, 0, 0, )));
    }
    @Test
    public void testLocale()
    {
        Locale locale = .;
        Session localeSession = Session.builder()
                .setUser("user")
                .setSource("test")
                .setCatalog("catalog")
                .setSchema("schema")
                .setTimeZoneKey()
                .setLocale(locale)
                .build();
        FunctionAssertions localeAssertions = new FunctionAssertions(localeSession);
        String dateTimeLiteral = "TIMESTAMP '2001-01-09 13:04:05.321'";
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%a')""火");
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%W')""火曜日");
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%p')""午後");
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%r')""01:04:05 午後");
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%b')""1");
        localeAssertions.assertFunction("date_format(" + dateTimeLiteral + ", '%M')""1月");
        localeAssertions.assertFunction("format_datetime(" + dateTimeLiteral + ", 'EEE')""火");
        localeAssertions.assertFunction("format_datetime(" + dateTimeLiteral + ", 'EEEE')""火曜日");
        localeAssertions.assertFunction("format_datetime(" + dateTimeLiteral + ", 'a')""午後");
        localeAssertions.assertFunction("format_datetime(" + dateTimeLiteral + ", 'MMM')""1");
        localeAssertions.assertFunction("format_datetime(" + dateTimeLiteral + ", 'MMMM')""1月");
        localeAssertions.assertFunction("date_parse('2013-05-17 12:35:10 午後', '%Y-%m-%d %h:%i:%s %p')"toTimestamp(new DateTime(2013, 5, 17, 12, 35, 10, 0, ), localeSession));
        localeAssertions.assertFunction("date_parse('2013-05-17 12:35:10 午前', '%Y-%m-%d %h:%i:%s %p')"toTimestamp(new DateTime(2013, 5, 17, 0, 35, 10, 0, ), localeSession));
        localeAssertions.assertFunction("parse_datetime('2013-05-17 12:35:10 午後', 'yyyy-MM-dd hh:mm:ss a')",
                toTimestampWithTimeZone(new DateTime(2013, 5, 17, 12, 35, 10, 0, )));
        localeAssertions.assertFunction("parse_datetime('2013-05-17 12:35:10 午前', 'yyyy-MM-dd hh:mm:ss aaa')",
                toTimestampWithTimeZone(new DateTime(2013, 5, 17, 0, 35, 10, 0, )));
    }
    private void assertFunction(String projectionObject expected)
    {
        .assertFunction(projectionexpected);
    }
    private SqlDate toDate(DateTime dateDate)
    {
        long millis = dateDate.getMillis();
        return new SqlDate((int..toDays(millis));
    }
    private SqlTime toTime(long milliseconds)
    {
        return new SqlTime(milliseconds.getTimeZoneKey());
    }
    private SqlTime toTime(DateTime dateTime)
    {
        return new SqlTime(dateTime.getMillis(), .getTimeZoneKey());
    }
    {
        return new SqlTimeWithTimeZone(dateTime.getMillis(), dateTime.getZone().toTimeZone());
    }
    private SqlTimestamp toTimestamp(long milliseconds)
    {
        return new SqlTimestamp(milliseconds.getTimeZoneKey());
    }
    private SqlTimestamp toTimestamp(DateTime dateTime)
    {
        return new SqlTimestamp(dateTime.getMillis(), .getTimeZoneKey());
    }
    private SqlTimestamp toTimestamp(DateTime dateTimeSession session)
    {
        return new SqlTimestamp(dateTime.getMillis(), session.getTimeZoneKey());
    }
    {
        return new SqlTimestampWithTimeZone(dateTime.getMillis(), dateTime.getZone().toTimeZone());
    }
New to GrepCode? Check out our FAQ X