Repository: prestodb/presto
Issue: Date constructor should handle year 0 correctly

Year zero is converted to January 2nd of 1 AD:

```
presto> select date '0000-12-31';
   _col0    
------------
 0001-01-02 
(1 row)
```

It should be rejected, similar to other invalid dates:

```
presto> select date '2015-02-29';
Query ... failed: Value cannot be cast to date: 2015-02-29

presto> select date '2016-12-32';
Query ... failed: Value cannot be cast to date: 2016-12-32
```


Changed files:
presto-main/src/test/java/com/facebook/presto/operator/scalar/TestDateTimeFunctions.java
presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestamp.java
presto-main/src/test/java/com/facebook/presto/operator/scalar/FunctionAssertions.java
presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestampWithTimeZone.java
presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTime.java
presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimeWithTimeZone.java
presto-spi/src/main/java/com/facebook/presto/spi/type/SqlDate.java

diff --git a/presto-main/src/test/java/com/facebook/presto/operator/scalar/FunctionAssertions.java b/presto-main/src/test/java/com/facebook/presto/operator/scalar/FunctionAssertions.java
index b478b32aa8..0cc40f4a94 100644
--- a/presto-main/src/test/java/com/facebook/presto/operator/scalar/FunctionAssertions.java
+++ b/presto-main/src/test/java/com/facebook/presto/operator/scalar/FunctionAssertions.java
@@ -232,6 +232,17 @@ public final class FunctionAssertions
         }
     }
 
+    public void assertFunctionString(String projection, Type expectedType, String expected)
+    {
+        Object actual = selectSingleValue(projection, expectedType, compiler);
+        try {
+            assertEquals(actual.toString(), expected);
+        }
+        catch (Throwable e) {
+            throw e;
+        }
+    }
+
     public void tryEvaluate(String expression, Type expectedType)
     {
         tryEvaluate(expression, expectedType, session);
diff --git a/presto-main/src/test/java/com/facebook/presto/operator/scalar/TestDateTimeFunctions.java b/presto-main/src/test/java/com/facebook/presto/operator/scalar/TestDateTimeFunctions.java
index 35d2801a5e..8c57fef0ce 100644
--- a/presto-main/src/test/java/com/facebook/presto/operator/scalar/TestDateTimeFunctions.java
+++ b/presto-main/src/test/java/com/facebook/presto/operator/scalar/TestDateTimeFunctions.java
@@ -820,11 +820,51 @@ public class TestDateTimeFunctions
                 toTimestampWithTimeZone(new DateTime(2013, 5, 17, 0, 35, 10, 0, DATE_TIME_ZONE)));
     }
 
+    @Test
+    public void testDateTimeOutputString()
+    {
+        // SqlDate
+        assertFunctionString("date '2012-12-31'", DateType.DATE, "2012-12-31");
+        assertFunctionString("date '0000-12-31'", DateType.DATE, "0000-12-31");
+        assertFunctionString("date '0000-09-23'", DateType.DATE, "0000-09-23");
+        assertFunctionString("date '0001-10-25'", DateType.DATE, "0001-10-25");
+        assertFunctionString("date '1560-04-29'", DateType.DATE, "1560-04-29");
+
+        // SqlTime
+        assertFunctionString("time '00:00:00'", TimeType.TIME, "00:00:00.000");
+        assertFunctionString("time '01:02:03'", TimeType.TIME, "01:02:03.000");
+        assertFunctionString("time '23:23:23.233'", TimeType.TIME, "23:23:23.233");
+        assertFunctionString("time '23:59:59.999'", TimeType.TIME, "23:59:59.999");
+
+        // SqlTimeWithTimeZone
+        assertFunctionString("time '00:00:00 UTC'", TIME_WITH_TIME_ZONE, "00:00:00.000 UTC");
+        assertFunctionString("time '01:02:03 Asia/Shanghai'", TIME_WITH_TIME_ZONE, "01:02:03.000 Asia/Shanghai");
+        assertFunctionString("time '23:23:23.233 America/Los_Angeles'", TIME_WITH_TIME_ZONE, "23:23:23.233 America/Los_Angeles");
+        assertFunctionString(WEIRD_TIME_LITERAL, TIME_WITH_TIME_ZONE, "03:04:05.321 +07:09");
+
+        // SqlTimestamp
+        assertFunctionString("timestamp '0000-01-02 01:02:03'", TimestampType.TIMESTAMP, "0000-01-02 01:02:03.000");
+        assertFunctionString("timestamp '2012-12-31 00:00:00'", TimestampType.TIMESTAMP, "2012-12-31 00:00:00.000");
+        assertFunctionString("timestamp '1234-05-06 23:23:23.233'", TimestampType.TIMESTAMP, "1234-05-06 23:23:23.233");
+        assertFunctionString("timestamp '2333-02-23 23:59:59.999'", TimestampType.TIMESTAMP, "2333-02-23 23:59:59.999");
+
+        // SqlTimestampWithTimeZone
+        assertFunctionString("timestamp '2012-12-31 00:00:00 UTC'", TIMESTAMP_WITH_TIME_ZONE, "2012-12-31 00:00:00.000 UTC");
+        assertFunctionString("timestamp '0000-01-02 01:02:03 Asia/Shanghai'", TIMESTAMP_WITH_TIME_ZONE, "0000-01-02 01:02:03.000 Asia/Shanghai");
+        assertFunctionString("timestamp '1234-05-06 23:23:23.233 America/Los_Angeles'", TIMESTAMP_WITH_TIME_ZONE, "1234-05-06 23:23:23.233 America/Los_Angeles");
+        assertFunctionString("timestamp '2333-02-23 23:59:59.999 Asia/Tokyo'", TIMESTAMP_WITH_TIME_ZONE, "2333-02-23 23:59:59.999 Asia/Tokyo");
+    }
+
     private void assertFunction(String projection, Type expectedType, Object expected)
     {
         functionAssertions.assertFunction(projection, expectedType, expected);
     }
 
+    private void assertFunctionString(String projection, Type expectedType, String expected)
+    {
+        functionAssertions.assertFunctionString(projection, expectedType, expected);
+    }
+
     private SqlDate toDate(DateTime dateDate)
     {
         long millis = dateDate.getMillis();
diff --git a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlDate.java b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlDate.java
index 4171f4a56b..6a13509b44 100644
--- a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlDate.java
+++ b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlDate.java
@@ -15,13 +15,8 @@ package com.facebook.presto.spi.type;
 
 import com.fasterxml.jackson.annotation.JsonValue;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.time.LocalDate;
 import java.util.Objects;
-import java.util.concurrent.TimeUnit;
-
-import static com.facebook.presto.spi.type.TimeZoneIndex.getTimeZoneForKey;
-import static com.facebook.presto.spi.type.TimeZoneKey.UTC_KEY;
 
 public final class SqlDate
 {
@@ -60,8 +55,6 @@ public final class SqlDate
     @Override
     public String toString()
     {
-        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
-        format.setTimeZone(getTimeZoneForKey(UTC_KEY));
-        return format.format(new Date(TimeUnit.DAYS.toMillis(days)));
+        return LocalDate.ofEpochDay(days).toString();
     }
 }
diff --git a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTime.java b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTime.java
index 8858ef09d2..3a883c7aa0 100644
--- a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTime.java
+++ b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTime.java
@@ -15,14 +15,15 @@ package com.facebook.presto.spi.type;
 
 import com.fasterxml.jackson.annotation.JsonValue;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.time.Instant;
+import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
 import java.util.Objects;
 
-import static com.facebook.presto.spi.type.TimeZoneIndex.getTimeZoneForKey;
-
 public final class SqlTime
 {
+    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss.SSS");
+
     private final long millisUtc;
     private final TimeZoneKey sessionTimeZoneKey;
 
@@ -66,8 +67,6 @@ public final class SqlTime
     @Override
     public String toString()
     {
-        SimpleDateFormat format = new SimpleDateFormat("HH:mm:ss.SSS");
-        format.setTimeZone(getTimeZoneForKey(sessionTimeZoneKey));
-        return format.format(new Date(millisUtc));
+        return Instant.ofEpochMilli(millisUtc).atZone(ZoneId.of(sessionTimeZoneKey.getId())).format(formatter);
     }
 }
diff --git a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimeWithTimeZone.java b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimeWithTimeZone.java
index e63867d8cd..ba09d16550 100644
--- a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimeWithTimeZone.java
+++ b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimeWithTimeZone.java
@@ -15,17 +15,19 @@ package com.facebook.presto.spi.type;
 
 import com.fasterxml.jackson.annotation.JsonValue;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.time.Instant;
+import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
 import java.util.Objects;
 import java.util.TimeZone;
 
 import static com.facebook.presto.spi.type.DateTimeEncoding.unpackMillisUtc;
 import static com.facebook.presto.spi.type.DateTimeEncoding.unpackZoneKey;
-import static com.facebook.presto.spi.type.TimeZoneIndex.getTimeZoneForKey;
 
 public final class SqlTimeWithTimeZone
 {
+    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss.SSS VV");
+
     private final long millisUtc;
     private final TimeZoneKey timeZoneKey;
 
@@ -81,8 +83,6 @@ public final class SqlTimeWithTimeZone
     @Override
     public String toString()
     {
-        SimpleDateFormat format = new SimpleDateFormat("HH:mm:ss.SSS");
-        format.setTimeZone(getTimeZoneForKey(timeZoneKey));
-        return format.format(new Date(millisUtc)) + " " + timeZoneKey.getId();
+        return Instant.ofEpochMilli(millisUtc).atZone(ZoneId.of(timeZoneKey.getId())).format(formatter);
     }
 }
diff --git a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestamp.java b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestamp.java
index eaa68b3c9f..6290daa6ea 100644
--- a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestamp.java
+++ b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestamp.java
@@ -15,14 +15,15 @@ package com.facebook.presto.spi.type;
 
 import com.fasterxml.jackson.annotation.JsonValue;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.time.Instant;
+import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
 import java.util.Objects;
 
-import static com.facebook.presto.spi.type.TimeZoneIndex.getTimeZoneForKey;
-
 public final class SqlTimestamp
 {
+    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS");
+
     private final long millisUtc;
     private final TimeZoneKey sessionTimeZoneKey;
 
@@ -66,8 +67,6 @@ public final class SqlTimestamp
     @Override
     public String toString()
     {
-        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
-        format.setTimeZone(getTimeZoneForKey(sessionTimeZoneKey));
-        return format.format(new Date(millisUtc));
+        return Instant.ofEpochMilli(millisUtc).atZone(ZoneId.of(sessionTimeZoneKey.getId())).format(formatter);
     }
 }
diff --git a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestampWithTimeZone.java b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestampWithTimeZone.java
index e171f42743..b9ad4eb7de 100644
--- a/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestampWithTimeZone.java
+++ b/presto-spi/src/main/java/com/facebook/presto/spi/type/SqlTimestampWithTimeZone.java
@@ -15,17 +15,19 @@ package com.facebook.presto.spi.type;
 
 import com.fasterxml.jackson.annotation.JsonValue;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.time.Instant;
+import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
 import java.util.Objects;
 import java.util.TimeZone;
 
 import static com.facebook.presto.spi.type.DateTimeEncoding.unpackMillisUtc;
 import static com.facebook.presto.spi.type.DateTimeEncoding.unpackZoneKey;
-import static com.facebook.presto.spi.type.TimeZoneIndex.getTimeZoneForKey;
 
 public final class SqlTimestampWithTimeZone
 {
+    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS VV");
+
     private final long millisUtc;
     private final TimeZoneKey timeZoneKey;
 
@@ -81,8 +83,6 @@ public final class SqlTimestampWithTimeZone
     @Override
     public String toString()
     {
-        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
-        format.setTimeZone(getTimeZoneForKey(timeZoneKey));
-        return format.format(new Date(millisUtc)) + " " + timeZoneKey.getId();
+        return Instant.ofEpochMilli(millisUtc).atZone(ZoneId.of(timeZoneKey.getId())).format(formatter);
     }
 }