Repository: gluesql/gluesql
Pull request #215 (2021-05-01 15:21:55)

Add DATE & TIMESTAMP data type
Implement `DATE` and `TIMESTAMP` data type!

`DATE` does not have time zone info.
`TIMESTAMP` stores values by converting into UTC time zone.
e.g. `2021-05-01T11:00:00+0900` -> `2021-05-01T02:00:00Z`

Though `INTERVAL` type is not implemented yet, comparison between `DATE and DATE`, `TIMESTAMP and TIMESTAMP` and `DATE and TIMESTAMP` are supported.


Patch:
diff --git a/Cargo.toml b/Cargo.toml
index 8e85f7b0..7230d87c 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -28,6 +28,7 @@ sled-storage = ["sled", "bincode"]
 async-trait = "0.1.41"
 async-recursion = "0.3.1"
 boolinator = "2.4.0"
+chrono = { version = "0.4", features = ["serde", "wasmbind"] }
 futures = "0.3"
 indexmap = "1.6.0"
 im-rc = "15.0.0"
diff --git a/src/data/value/error.rs b/src/data/value/error.rs
index 97f585be..79313b56 100644
--- a/src/data/value/error.rs
+++ b/src/data/value/error.rs
@@ -14,6 +14,12 @@ pub enum ValueError {
     #[error("failed to parse number")]
     FailedToParseNumber,
 
+    #[error("failed to parse date: {0}")]
+    FailedToParseDate(String),
+
+    #[error("failed to parse timestamp: {0}")]
+    FailedToParseTimestamp(String),
+
     #[error("add on non-numeric values: {0} + {1}")]
     AddOnNonNumeric(String, String),
 
diff --git a/src/data/value/group_key.rs b/src/data/value/group_key.rs
index 566a936b..c6687d01 100644
--- a/src/data/value/group_key.rs
+++ b/src/data/value/group_key.rs
@@ -17,6 +17,8 @@ impl TryInto<GroupKey> for &Value {
             Bool(v) => Ok(GroupKey::Bool(*v)),
             I64(v) => Ok(GroupKey::I64(*v)),
             Str(v) => Ok(GroupKey::Str(v.clone())),
+            Date(v) => Ok(GroupKey::Date(*v)),
+            Timestamp(v) => Ok(GroupKey::Timestamp(*v)),
             Null => Ok(GroupKey::None),
             F64(_) => Err(ValueError::FloatCannotBeGroupedBy.into()),
         }
@@ -33,6 +35,8 @@ impl TryInto<GroupKey> for Value {
             Bool(v) => Ok(GroupKey::Bool(v)),
             I64(v) => Ok(GroupKey::I64(v)),
             Str(v) => Ok(GroupKey::Str(v)),
+            Date(v) => Ok(GroupKey::Date(v)),
+            Timestamp(v) => Ok(GroupKey::Timestamp(v)),
             Null => Ok(GroupKey::None),
             F64(_) => Err(ValueError::FloatCannotBeGroupedBy.into()),
         }
diff --git a/src/data/value/into.rs b/src/data/value/into.rs
index d0b6cc65..fb813238 100644
--- a/src/data/value/into.rs
+++ b/src/data/value/into.rs
@@ -1,6 +1,7 @@
 use {
     super::{Value, ValueError},
     crate::result::{Error, Result},
+    chrono::{NaiveDate, NaiveDateTime},
     std::convert::TryInto,
 };
 
@@ -11,6 +12,8 @@ impl From<&Value> for String {
             Value::Bool(value) => (if *value { "TRUE" } else { "FALSE" }).to_string(),
             Value::I64(value) => value.to_string(),
             Value::F64(value) => value.to_string(),
+            Value::Date(value) => value.to_string(),
+            Value::Timestamp(value) => value.to_string(),
             Value::Null => String::from("NULL"),
         }
     }
@@ -50,7 +53,9 @@ impl TryInto<bool> for &Value {
                 "FALSE" => false,
                 _ => return Err(ValueError::ImpossibleCast.into()),
             },
-            Value::Null => return Err(ValueError::ImpossibleCast.into()),
+            Value::Date(_) | Value::Timestamp(_) | Value::Null => {
+                return Err(ValueError::ImpossibleCast.into())
+            }
         })
     }
 }
@@ -80,7 +85,9 @@ impl TryInto<i64> for &Value {
             Value::Str(value) => value
                 .parse::<i64>()
                 .map_err(|_| ValueError::ImpossibleCast)?,
-            Value::Null => return Err(ValueError::ImpossibleCast.into()),
+            Value::Date(_) | Value::Timestamp(_) | Value::Null => {
+                return Err(ValueError::ImpossibleCast.into())
+            }
         })
     }
 }
@@ -102,7 +109,33 @@ impl TryInto<f64> for &Value {
             Value::Str(value) => value
                 .parse::<f64>()
                 .map_err(|_| ValueError::ImpossibleCast)?,
-            Value::Null => return Err(ValueError::ImpossibleCast.into()),
+            Value::Date(_) | Value::Timestamp(_) | Value::Null => {
+                return Err(ValueError::ImpossibleCast.into())
+            }
+        })
+    }
+}
+
+impl TryInto<NaiveDate> for &Value {
+    type Error = Error;
+
+    fn try_into(self) -> Result<NaiveDate> {
+        Ok(match self {
+            Value::Date(value) => *value,
+            Value::Timestamp(value) => value.date(),
+            _ => return Err(ValueError::ImpossibleCast.into()),
+        })
+    }
+}
+
+impl TryInto<NaiveDateTime> for &Value {
+    type Error = Error;
+
+    fn try_into(self) -> Result<NaiveDateTime> {
+        Ok(match self {
+            Value::Date(value) => value.and_hms(0, 0, 0),
+            Value::Timestamp(value) => *value,
+            _ => return Err(ValueError::ImpossibleCast.into()),
         })
     }
 }
diff --git a/src/data/value/literal.rs b/src/data/value/literal.rs
index 0420e16c..16b82b4f 100644
--- a/src/data/value/literal.rs
+++ b/src/data/value/literal.rs
@@ -4,6 +4,7 @@ use {
         data::Literal,
         result::{Error, Result},
     },
+    chrono::{offset::Utc, DateTime, NaiveDate, NaiveDateTime},
     sqlparser::ast::DataType,
     std::{cmp::Ordering, convert::TryFrom},
 };
@@ -27,6 +28,14 @@ impl PartialEq<Literal<'_>> for Value {
                 },
             },
             (Value::Str(l), Literal::Text(r)) => l == r.as_ref(),
+            (Value::Date(l), Literal::Text(r)) => match r.parse::<NaiveDate>() {
+                Ok(r) => l == &r,
+                Err(_) => false,
+            },
+            (Value::Timestamp(l), Literal::Text(r)) => match parse_timestamp(r) {
+                Ok(r) => l == &r,
+                Err(_) => false,
+            },
             _ => false,
         }
     }
@@ -50,6 +59,14 @@ impl PartialOrd<Literal<'_>> for Value {
                 },
             },
             (Value::Str(l), Literal::Text(r)) => Some(l.cmp(r.as_ref())),
+            (Value::Date(l), Literal::Text(r)) => match r.parse::<NaiveDate>() {
+                Ok(r) => l.partial_cmp(&r),
+                Err(_) => None,
+            },
+            (Value::Timestamp(l), Literal::Text(r)) => match parse_timestamp(r) {
+                Ok(r) => l.partial_cmp(&r),
+                Err(_) => None,
+            },
             _ => None,
         }
     }
@@ -106,10 +123,19 @@ impl TryFromLiteral for Value {
                 .map(Value::F64)
                 .map_err(|_| ValueError::UnreachableNumberParsing.into()),
             (DataType::Text, Literal::Text(v)) => Ok(Value::Str(v.to_string())),
+            (DataType::Date, Literal::Text(v)) => v
+                .parse::<NaiveDate>()
+                .map(Value::Date)
+                .map_err(|_| ValueError::FailedToParseDate(v.to_string()).into()),
+            (DataType::Timestamp, Literal::Text(v)) => parse_timestamp(v)
+                .map(Value::Timestamp)
+                .map_err(|_| ValueError::FailedToParseTimestamp(v.to_string()).into()),
             (DataType::Boolean, Literal::Null)
             | (DataType::Int, Literal::Null)
             | (DataType::Float(_), Literal::Null)
-            | (DataType::Text, Literal::Null) => Ok(Value::Null),
+            | (DataType::Text, Literal::Null)
+            | (DataType::Date, Literal::Null)
+            | (DataType::Timestamp, Literal::Null) => Ok(Value::Null),
             _ => Err(ValueError::IncompatibleLiteralForDataType {
                 data_type: data_type.to_string(),
                 literal: format!("{:?}", literal),
@@ -172,3 +198,15 @@ impl TryFromLiteral for Value {
         }
     }
 }
+
+fn parse_timestamp(v: &str) -> Result<NaiveDateTime> {
+    if let Ok(v) = v.parse::<DateTime<Utc>>() {
+        Ok(v.naive_utc())
+    } else if let Ok(v) = v.parse::<NaiveDateTime>() {
+        Ok(v)
+    } else if let Ok(v) = v.parse::<NaiveDate>() {
+        Ok(v.and_hms(0, 0, 0))
+    } else {
+        Err(ValueError::FailedToParseTimestamp(v.to_string()).into())
+    }
+}
diff --git a/src/data/value/mod.rs b/src/data/value/mod.rs
index 9477928c..3b7bec48 100644
--- a/src/data/value/mod.rs
+++ b/src/data/value/mod.rs
@@ -1,6 +1,7 @@
 use {
     super::Literal,
     crate::result::Result,
+    chrono::{NaiveDate, NaiveDateTime},
     serde::{Deserialize, Serialize},
     sqlparser::ast::{DataType, Expr},
     std::{
@@ -24,6 +25,8 @@ pub enum Value {
     I64(i64),
     F64(f64),
     Str(String),
+    Date(NaiveDate),
+    Timestamp(NaiveDateTime),
     Null,
 }
 
@@ -34,6 +37,10 @@ impl PartialEq<Value> for Value {
             (Value::I64(l), Value::I64(r)) => l == r,
             (Value::F64(l), Value::F64(r)) => l == r,
             (Value::Str(l), Value::Str(r)) => l == r,
+            (Value::Date(l), Value::Date(r)) => l == r,
+            (Value::Date(l), Value::Timestamp(r)) => &l.and_hms(0, 0, 0) == r,
+            (Value::Timestamp(l), Value::Date(r)) => l == &r.and_hms(0, 0, 0),
+            (Value::Timestamp(l), Value::Timestamp(r)) => l == r,
             _ => false,
         }
     }
@@ -48,6 +55,10 @@ impl PartialOrd<Value> for Value {
             (Value::F64(l), Value::I64(r)) => l.partial_cmp(&(*r as f64)),
             (Value::F64(l), Value::F64(r)) => l.partial_cmp(r),
             (Value::Str(l), Value::Str(r)) => Some(l.cmp(r)),
+            (Value::Date(l), Value::Date(r)) => l.partial_cmp(r),
+            (Value::Date(l), Value::Timestamp(r)) => l.and_hms(0, 0, 0).partial_cmp(r),
+            (Value::Timestamp(l), Value::Date(r)) => l.partial_cmp(&r.and_hms(0, 0, 0)),
+            (Value::Timestamp(l), Value::Timestamp(r)) => l.partial_cmp(r),
             _ => None,
         }
     }
@@ -85,10 +96,14 @@ impl Value {
                 | (DataType::Int, Value::I64(_))
                 | (DataType::Float(_), Value::F64(_))
                 | (DataType::Text, Value::Str(_))
+                | (DataType::Date, Value::Date(_))
+                | (DataType::Timestamp, Value::Timestamp(_))
                 | (DataType::Boolean, Value::Null)
                 | (DataType::Int, Value::Null)
                 | (DataType::Float(_), Value::Null)
                 | (DataType::Text, Value::Null)
+                | (DataType::Date, Value::Null)
+                | (DataType::Timestamp, Value::Null)
         );
 
         if !valid {
@@ -115,13 +130,18 @@ impl Value {
             (DataType::Boolean, Value::Bool(_))
             | (DataType::Int, Value::I64(_))
             | (DataType::Float(_), Value::F64(_))
-            | (DataType::Text, Value::Str(_)) => Ok(self.clone()),
+            | (DataType::Text, Value::Str(_))
+            | (DataType::Date, Value::Date(_))
+            | (DataType::Timestamp, Value::Timestamp(_)) => Ok(self.clone()),
+
             (_, Value::Null) => Ok(Value::Null),
 
             (DataType::Boolean, value) => value.try_into().map(Value::Bool),
             (DataType::Int, value) => value.try_into().map(Value::I64),
             (DataType::Float(_), value) => value.try_into().map(Value::F64),
             (DataType::Text, value) => Ok(Value::Str(value.into())),
+            (DataType::Date, value) => value.try_into().map(Value::Date),
+            (DataType::Timestamp, value) => value.try_into().map(Value::Timestamp),
 
             _ => Err(ValueError::UnimplementedCast.into()),
         }
@@ -237,11 +257,31 @@ mod tests {
 
     #[test]
     fn eq() {
+        use chrono::NaiveDateTime;
+
         assert_ne!(Null, Null);
         assert_eq!(Bool(true), Bool(true));
         assert_eq!(I64(1), I64(1));
         assert_eq!(F64(6.11), F64(6.11));
         assert_eq!(Str("Glue".to_owned()), Str("Glue".to_owned()));
+
+        let date = Date("2020-05-01".parse().unwrap());
+        let timestamp = Timestamp("2020-05-01T00:00:00".parse::<NaiveDateTime>().unwrap());
+
+        assert_eq!(date, timestamp);
+        assert_eq!(timestamp, date);
+    }
+
+    #[test]
+    fn cmp() {
+        use chrono::NaiveDate;
+        use std::cmp::Ordering;
+
+        let date = Date(NaiveDate::from_ymd(2020, 5, 1));
+        let timestamp = Timestamp(NaiveDate::from_ymd(2020, 3, 1).and_hms(0, 0, 0));
+
+        assert_eq!(date.partial_cmp(&timestamp), Some(Ordering::Greater));
+        assert_eq!(timestamp.partial_cmp(&date), Some(Ordering::Less));
     }
 
     #[test]
@@ -304,7 +344,7 @@ mod tests {
 
     #[test]
     fn cast() {
-        use sqlparser::ast::DataType::*;
+        use {crate::Value, chrono::NaiveDate, sqlparser::ast::DataType::*};
 
         macro_rules! cast {
             ($input: expr => $data_type: expr, $expected: expr) => {
@@ -353,7 +393,27 @@ mod tests {
         cast!(Bool(false)   => Text, Str("FALSE".to_owned()));
         cast!(I64(11)       => Text, Str("11".to_owned()));
         cast!(F64(1.0)      => Text, Str("1".to_owned()));
+
+        let date = Value::Date(NaiveDate::from_ymd(2021, 5, 1));
+        cast!(date          => Text, Str("2021-05-01".to_owned()));
+
+        let timestamp = Value::Timestamp(NaiveDate::from_ymd(2021, 5, 1).and_hms(12, 34, 50));
+        cast!(timestamp     => Text, Str("2021-05-01 12:34:50".to_owned()));
         cast!(Null          => Text, Null);
+
+        // Date
+        let date = Value::Date(NaiveDate::from_ymd(2021, 5, 1));
+        let timestamp = Value::Timestamp(NaiveDate::from_ymd(2021, 5, 1).and_hms(12, 34, 50));
+
+        cast!(timestamp => Date, date);
+        cast!(Null      => Date, Null);
+
+        // Timestamp
+        let date = Value::Date(NaiveDate::from_ymd(2021, 5, 1));
+        let timestamp = Value::Timestamp(NaiveDate::from_ymd(2021, 5, 1).and_hms(0, 0, 0));
+
+        cast!(date => Timestamp, timestamp);
+        cast!(Null => Timestamp, Null);
     }
 
     #[test]
diff --git a/src/data/value/unique_key.rs b/src/data/value/unique_key.rs
index 6f8e5824..835f11e5 100644
--- a/src/data/value/unique_key.rs
+++ b/src/data/value/unique_key.rs
@@ -17,6 +17,8 @@ impl TryInto<Option<UniqueKey>> for &Value {
             Bool(v) => Some(UniqueKey::Bool(*v)),
             I64(v) => Some(UniqueKey::I64(*v)),
             Str(v) => Some(UniqueKey::Str(v.clone())),
+            Date(v) => Some(UniqueKey::Date(*v)),
+            Timestamp(v) => Some(UniqueKey::Timestamp(*v)),
             Null => None,
             F64(_) => {
                 return Err(ValueError::ConflictOnFloatWithUniqueConstraint.into());
diff --git a/src/executor/aggregate/hash.rs b/src/executor/aggregate/hash.rs
index cf69cc41..87d9cb9f 100644
--- a/src/executor/aggregate/hash.rs
+++ b/src/executor/aggregate/hash.rs
@@ -1,14 +1,20 @@
-use std::convert::{TryFrom, TryInto};
-
-use crate::data::Value;
-use crate::executor::evaluate::Evaluated;
-use crate::result::{Error, Result};
+use {
+    crate::{
+        data::Value,
+        executor::evaluate::Evaluated,
+        result::{Error, Result},
+    },
+    chrono::{NaiveDate, NaiveDateTime},
+    std::convert::{TryFrom, TryInto},
+};
 
 #[derive(PartialEq, Eq, Hash, Clone, std::fmt::Debug)]
 pub enum GroupKey {
     I64(i64),
     Bool(bool),
     Str(String),
+    Date(NaiveDate),
+    Timestamp(NaiveDateTime),
     None,
 }
 
diff --git a/src/executor/alter/validate.rs b/src/executor/alter/validate.rs
index 1890bcda..cfd12dd9 100644
--- a/src/executor/alter/validate.rs
+++ b/src/executor/alter/validate.rs
@@ -15,7 +15,12 @@ pub fn validate(column_def: &ColumnDef) -> Result<()> {
     // data type
     if !matches!(
         data_type,
-        DataType::Boolean | DataType::Int | DataType::Float(_) | DataType::Text
+        DataType::Boolean
+            | DataType::Int
+            | DataType::Float(_)
+            | DataType::Text
+            | DataType::Date
+            | DataType::Timestamp
     ) {
         return Err(AlterError::UnsupportedDataType(data_type.to_string()).into());
     }
diff --git a/src/executor/evaluate/mod.rs b/src/executor/evaluate/mod.rs
index 8b93f629..698315af 100644
--- a/src/executor/evaluate/mod.rs
+++ b/src/executor/evaluate/mod.rs
@@ -4,7 +4,7 @@ mod evaluated;
 use {
     super::{context::FilterContext, select::select},
     crate::{
-        data::{get_name, Literal, Value},
+        data::{get_name, value::TryFromLiteral, Literal, Value},
         result::Result,
         store::Store,
     },
@@ -39,7 +39,12 @@ pub async fn evaluate<'a, T: 'static + Debug>(
     };
 
     match expr {
-        Expr::Value(value) => Literal::try_from(value).map(Evaluated::Literal),
+        Expr::Value(ast_value) => Literal::try_from(ast_value).map(Evaluated::Literal),
+        Expr::TypedString { data_type, value } => {
+            let literal = Literal::Text(Cow::Borrowed(value));
+
+            Value::try_from_literal(&data_type, &literal).map(Evaluated::from)
+        }
         Expr::Identifier(ident) => match ident.quote_style {
             Some(_) => Ok(Literal::Text(Cow::Borrowed(&ident.value))).map(Evaluated::Literal),
             None => {
diff --git a/src/executor/validate.rs b/src/executor/validate.rs
index bfc4abd3..2fd225df 100644
--- a/src/executor/validate.rs
+++ b/src/executor/validate.rs
@@ -6,6 +6,7 @@ use {
         utils::Vector,
     },
     boolinator::Boolinator,
+    chrono::{NaiveDate, NaiveDateTime},
     im_rc::HashSet,
     serde::Serialize,
     sqlparser::ast::{ColumnDef, ColumnOption, Ident},
@@ -32,6 +33,8 @@ pub enum UniqueKey {
     Bool(bool),
     I64(i64),
     Str(String),
+    Date(NaiveDate),
+    Timestamp(NaiveDateTime),
 }
 
 #[derive(Debug)]
diff --git a/src/lib.rs b/src/lib.rs
index b43a2951..04666781 100644
--- a/src/lib.rs
+++ b/src/lib.rs
@@ -61,6 +61,7 @@
 //! After you implement `Tester` trait, the only thing you need to do is calling `generate_tests!` macro.
 
 // re-export
+pub use chrono;
 pub use sqlparser as parser;
 
 mod executor;


Test patch:
diff --git a/src/tests/date.rs b/src/tests/date.rs
new file mode 100644
index 00000000..b860c1f2
--- /dev/null
+++ b/src/tests/date.rs
@@ -0,0 +1,93 @@
+use crate::*;
+
+test_case!(date, async move {
+    run!(
+        r#"
+CREATE TABLE DateLog (
+    id INTEGER,
+    date1 DATE,
+    date2 DATE,
+)"#
+    );
+
+    run!(
+        r#"
+INSERT INTO DateLog VALUES
+    (1, "2020-06-11", "2021-03-01"),
+    (2, "2020-09-30", "1989-01-01"),
+    (3, "2021-05-01", "2021-05-01");
+"#
+    );
+
+    use Value::*;
+
+    macro_rules! date {
+        ($date: expr) => {
+            $date.parse().unwrap()
+        };
+    }
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            1     date!("2020-06-11")   date!("2021-03-01");
+            2     date!("2020-09-30")   date!("1989-01-01");
+            3     date!("2021-05-01")   date!("2021-05-01")
+        )),
+        "SELECT id, date1, date2 FROM DateLog"
+    );
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            2     date!("2020-09-30")   date!("1989-01-01")
+        )),
+        "SELECT * FROM DateLog WHERE date1 > date2"
+    );
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            1     date!("2020-06-11")   date!("2021-03-01");
+            3     date!("2021-05-01")   date!("2021-05-01")
+        )),
+        "SELECT * FROM DateLog WHERE date1 <= date2"
+    );
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            1     date!("2020-06-11")   date!("2021-03-01")
+        )),
+        r#"SELECT * FROM DateLog WHERE date1 = DATE "2020-06-11";"#
+    );
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            2     date!("2020-09-30")   date!("1989-01-01")
+        )),
+        r#"SELECT * FROM DateLog WHERE date2 < "2000-01-01";"#
+    );
+
+    test!(
+        Ok(select!(
+            id  | date1               | date2
+            I64 | Date                | Date;
+            1     date!("2020-06-11")   date!("2021-03-01");
+            2     date!("2020-09-30")   date!("1989-01-01");
+            3     date!("2021-05-01")   date!("2021-05-01")
+        )),
+        r#"SELECT * FROM DateLog WHERE "1999-01-03" < DATE "2000-01-01";"#
+    );
+
+    test!(
+        Err(ValueError::FailedToParseDate("12345-678".to_owned()).into()),
+        r#"INSERT INTO DateLog VALUES (1, "12345-678", "2021-05-01")"#
+    );
+});
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index b58d47ae..63ac7e97 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -6,6 +6,7 @@ pub mod basic;
 pub mod blend;
 pub mod concat;
 pub mod create_table;
+pub mod date;
 pub mod default;
 pub mod drop_table;
 pub mod error;
@@ -18,6 +19,7 @@ pub mod nullable;
 pub mod ordering;
 pub mod sql_types;
 pub mod synthesize;
+pub mod timestamp;
 pub mod validate;
 
 mod tester;
@@ -80,6 +82,8 @@ macro_rules! generate_tests {
         glue!(nullable_text, nullable::nullable_text);
         glue!(ordering, ordering::ordering);
         glue!(sql_types, sql_types::sql_types);
+        glue!(date, date::date);
+        glue!(timestamp, timestamp::timestamp);
         glue!(synthesize, synthesize::synthesize);
         glue!(validate_unique, validate::unique::unique);
         glue!(validate_types, validate::types::types);
diff --git a/src/tests/timestamp.rs b/src/tests/timestamp.rs
new file mode 100644
index 00000000..c9b4d461
--- /dev/null
+++ b/src/tests/timestamp.rs
@@ -0,0 +1,93 @@
+use crate::*;
+
+test_case!(timestamp, async move {
+    run!(
+        r#"
+CREATE TABLE TimestampLog (
+    id INTEGER,
+    t1 TIMESTAMP,
+    t2 TIMESTAMP,
+)"#
+    );
+
+    run!(
+        r#"
+INSERT INTO TimestampLog VALUES
+    (1, "2020-06-11 11:23:11Z",           "2021-03-01"),
+    (2, "2020-09-30 12:00:00 -07:00",     "1989-01-01T00:01:00+09:00"),
+    (3, "2021-04-30T07:00:00.1234-17:00", "2021-05-01T09:00:00.1234+09:00");
+"#
+    );
+
+    macro_rules! t {
+        ($timestamp: expr) => {
+            $timestamp.parse().unwrap()
+        };
+    }
+
+    use Value::*;
+
+    test!(
+        Ok(select!(
+            id  | t1                             | t2
+            I64 | Timestamp                      | Timestamp;
+            1     t!("2020-06-11T11:23:11")        t!("2021-03-01T00:00:00");
+            2     t!("2020-09-30T19:00:00")        t!("1988-12-31T15:01:00");
+            3     t!("2021-05-01T00:00:00.1234")   t!("2021-05-01T00:00:00.1234")
+        )),
+        "SELECT id, t1, t2 FROM TimestampLog"
+    );
+
+    test!(
+        Ok(select!(
+            id  | t1                        | t2
+            I64 | Timestamp                 | Timestamp;
+            2     t!("2020-09-30T19:00:00")   t!("1988-12-31T15:01:00")
+        )),
+        "SELECT * FROM TimestampLog WHERE t1 > t2"
+    );
+
+    test!(
+        Ok(select!(
+            id  | t1                             | t2
+            I64 | Timestamp                      | Timestamp;
+            3     t!("2021-05-01T00:00:00.1234")   t!("2021-05-01T00:00:00.1234")
+        )),
+        "SELECT * FROM TimestampLog WHERE t1 = t2"
+    );
+
+    test!(
+        Ok(select!(
+            id  | t1                        | t2
+            I64 | Timestamp                 | Timestamp;
+            1     t!("2020-06-11T11:23:11")   t!("2021-03-01T00:00:00")
+
+        )),
+        r#"SELECT * FROM TimestampLog WHERE t1 = "2020-06-11T14:23:11+0300";"#
+    );
+
+    test!(
+        Ok(select!(
+            id  | t1                        | t2
+            I64 | Timestamp                 | Timestamp;
+            2     t!("2020-09-30T19:00:00")   t!("1988-12-31T15:01:00")
+        )),
+        r#"SELECT * FROM TimestampLog WHERE t2 < TIMESTAMP "2000-01-01";"#
+    );
+
+    test!(
+        Ok(select!(
+            id  | t1                             | t2
+            I64 | Timestamp                      | Timestamp;
+            1     t!("2020-06-11T11:23:11")        t!("2021-03-01T00:00:00");
+            2     t!("2020-09-30T19:00:00")        t!("1988-12-31T15:01:00");
+            3     t!("2021-05-01T00:00:00.1234")   t!("2021-05-01T00:00:00.1234")
+        )),
+        r#"SELECT * FROM TimestampLog WHERE TIMESTAMP "1999-01-03" < "2000-01-01";"#
+    );
+
+    test!(
+        Err(ValueError::FailedToParseTimestamp("12345-678".to_owned()).into()),
+        r#"INSERT INTO TimestampLog VALUES (1, "12345-678", "2021-05-01")"#
+    );
+});