MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMPbb-10.4-mdev16991

27 files changed, 1368 insertions, 358 deletions

diff --git a/sql/event_data_objects.cc b/sql/event_data_objects.cc
index a6803982171..db056a9f08e 100644
--- a/sql/event_data_objects.cc
+++ b/sql/event_data_objects.cc
@@ -479,14 +479,24 @@ Event_queue_element::load_from_row(THD *thd, TABLE *table)
   uint not_used;
   if (!starts_null)
   {
-    table->field[ET_FIELD_STARTS]->get_date(&time, TIME_NO_ZERO_DATE);
+    /*
+      The expected data type for these columns in mysql.events:
+        starts, ends, execute_at, last_executed
+      is DATETIME. No nanosecond truncation should normally be needed,
+      unless the DBA changes them, e.g. to VARCHAR, DECIMAL, etc.
+      For this unexpected case let's use the default round mode,
+      according to the current session settings.
+    */
+    table->field[ET_FIELD_STARTS]->get_date(&time, TIME_NO_ZERO_DATE |
+                                                   thd->temporal_round_mode());
     starts= my_tz_OFFSET0->TIME_to_gmt_sec(&time,&not_used);
   }
 
   ends_null= table->field[ET_FIELD_ENDS]->is_null();
   if (!ends_null)
   {
-    table->field[ET_FIELD_ENDS]->get_date(&time, TIME_NO_ZERO_DATE);
+    table->field[ET_FIELD_ENDS]->get_date(&time, TIME_NO_ZERO_DATE |
+                                                 thd->temporal_round_mode());
     ends= my_tz_OFFSET0->TIME_to_gmt_sec(&time,&not_used);
   }
 
@@ -502,8 +512,8 @@ Event_queue_element::load_from_row(THD *thd, TABLE *table)
   DBUG_ASSERT(!(starts_null && ends_null && !expression && execute_at_null));
   if (!expression && !execute_at_null)
   {
-    if (table->field[ET_FIELD_EXECUTE_AT]->get_date(&time,
-                                                    TIME_NO_ZERO_DATE))
+    if (table->field[ET_FIELD_EXECUTE_AT]->get_date(&time, TIME_NO_ZERO_DATE |
+                                                    thd->temporal_round_mode()))
       DBUG_RETURN(TRUE);
     execute_at= my_tz_OFFSET0->TIME_to_gmt_sec(&time,&not_used);
   }
@@ -535,8 +545,8 @@ Event_queue_element::load_from_row(THD *thd, TABLE *table)
 
   if (!table->field[ET_FIELD_LAST_EXECUTED]->is_null())
   {
-    table->field[ET_FIELD_LAST_EXECUTED]->get_date(&time,
-                                                   TIME_NO_ZERO_DATE);
+    table->field[ET_FIELD_LAST_EXECUTED]->get_date(&time, TIME_NO_ZERO_DATE |
+                                                   thd->temporal_round_mode());
     last_executed= my_tz_OFFSET0->TIME_to_gmt_sec(&time,&not_used);
   }
 
diff --git a/sql/event_parse_data.cc b/sql/event_parse_data.cc
index bfda8438885..00d625879de 100644
--- a/sql/event_parse_data.cc
+++ b/sql/event_parse_data.cc
@@ -216,7 +216,13 @@ Event_parse_data::init_execute_at(THD *thd)
                       (starts_null && ends_null)));
   DBUG_ASSERT(starts_null && ends_null);
 
-  if (item_execute_at->get_date(thd, &ltime, TIME_NO_ZERO_DATE))
+  /*
+    The expected data type is DATETIME. No nanoseconds truncation should
+    normally be needed. Using the default rounding mode.
+    See more comments in event_data_object.cc.
+  */
+  if (item_execute_at->get_date(thd, &ltime, TIME_NO_ZERO_DATE |
+                                             thd->temporal_round_mode()))
     goto wrong_value;
 
   ltime_utc= TIME_to_timestamp(thd,&ltime,&not_used);
@@ -378,7 +384,8 @@ Event_parse_data::init_starts(THD *thd)
   if (item_starts->fix_fields(thd, &item_starts))
     goto wrong_value;
 
-  if (item_starts->get_date(thd, &ltime, TIME_NO_ZERO_DATE))
+  if (item_starts->get_date(thd, &ltime, TIME_NO_ZERO_DATE |
+                                         thd->temporal_round_mode()))
     goto wrong_value;
 
   ltime_utc= TIME_to_timestamp(thd, &ltime, &not_used);
@@ -433,7 +440,8 @@ Event_parse_data::init_ends(THD *thd)
     goto error_bad_params;
 
   DBUG_PRINT("info", ("convert to TIME"));
-  if (item_ends->get_date(thd, &ltime, TIME_NO_ZERO_DATE))
+  if (item_ends->get_date(thd, &ltime, TIME_NO_ZERO_DATE |
+                                       thd->temporal_round_mode()))
     goto error_bad_params;
 
   ltime_utc= TIME_to_timestamp(thd, &ltime, &not_used);
diff --git a/sql/field.cc b/sql/field.cc
index cc95a8c8e84..21d4aea6fb4 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5084,10 +5084,11 @@ int Field_timestamp::store_TIME_with_warning(THD *thd, const Datetime *dt,
 }
 
 
-date_mode_t Field_timestamp::sql_mode_for_timestamp(THD *thd) const
+date_conv_mode_t Timestamp::sql_mode_for_timestamp(THD *thd)
 {
   // We don't want to store invalid or fuzzy datetime values in TIMESTAMP
-  return date_mode_t((thd->variables.sql_mode & MODE_NO_ZERO_DATE) | MODE_NO_ZERO_IN_DATE);
+  return date_conv_mode_t((thd->variables.sql_mode & MODE_NO_ZERO_DATE) |
+                          MODE_NO_ZERO_IN_DATE);
 }
 
 
@@ -5096,7 +5097,7 @@ int Field_timestamp::store_time_dec(const MYSQL_TIME *ltime, uint dec)
   int warn;
   ErrConvTime str(ltime);
   THD *thd= get_thd();
-  Datetime dt(thd, &warn, ltime, sql_mode_for_timestamp(thd), decimals());
+  Datetime dt(thd, &warn, ltime, Timestamp::DatetimeOptions(thd), decimals());
   return store_TIME_with_warning(thd, &dt, &str, warn);
 }
 
@@ -5106,7 +5107,7 @@ int Field_timestamp::store(const char *from,size_t len,CHARSET_INFO *cs)
   ErrConvString str(from, len, cs);
   THD *thd= get_thd();
   MYSQL_TIME_STATUS st;
-  Datetime dt(&st, from, len, cs, sql_mode_for_timestamp(thd), decimals());
+  Datetime dt(thd, &st, from, len, cs, Timestamp::DatetimeOptions(thd), decimals());
   return store_TIME_with_warning(thd, &dt, &str, st.warnings);
 }
 
@@ -5116,7 +5117,7 @@ int Field_timestamp::store(double nr)
   int error;
   ErrConvDouble str(nr);
   THD *thd= get_thd();
-  Datetime dt(&error, nr, sql_mode_for_timestamp(thd), decimals());
+  Datetime dt(thd, &error, nr, Timestamp::DatetimeOptions(thd), decimals());
   return store_TIME_with_warning(thd, &dt, &str, error);
 }
 
@@ -5127,14 +5128,29 @@ int Field_timestamp::store(longlong nr, bool unsigned_val)
   Longlong_hybrid tmp(nr, unsigned_val);
   ErrConvInteger str(tmp);
   THD *thd= get_thd();
-  Datetime dt(&error, tmp, sql_mode_for_timestamp(thd));
+  Datetime dt(thd, &error, tmp, Timestamp::DatetimeOptions(thd));
   return store_TIME_with_warning(thd, &dt, &str, error);
 }
 
 
 int Field_timestamp::store_timestamp(my_time_t ts, ulong sec_part)
 {
-  store_TIMESTAMP(Timestamp(ts, sec_part).trunc(decimals()));
+  int warn= 0;
+  time_round_mode_t mode= Datetime::default_round_mode(get_thd());
+  store_TIMESTAMP(Timestamp(ts, sec_part).round(decimals(), mode, &warn));
+  if (warn)
+  {
+    /*
+      We're here if rounding would overflow outside of the supported TIMESTAMP
+      range, so truncation happened instead:
+        CREATE TABLE t1 (a TIMESTAMP(6));
+        INSERT INTO t1 VALUES ('maximum-possible-timestamp.999999');
+        ALTER TABLE t1 MODIFY a TIMESTAMP(5);
+        SELECT * FROM t1; --> 'maximum-possible-timestamp.99999' (5 digits)
+      Raise a warning, like DATETIME does for '9999-12-31 23:59:59.999999'.
+    */
+    set_warning(Sql_condition::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1);
+  }
   if (ts == 0 && sec_part == 0 &&
       get_thd()->variables.sql_mode & (ulonglong) TIME_NO_ZERO_DATE)
   {
@@ -5157,7 +5173,7 @@ double Field_timestamp::val_real(void)
 longlong Field_timestamp::val_int(void)
 {
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_date(&ltime, Datetime::Options(TIME_NO_ZERO_DATE, get_thd())))
     return 0;
 
   return ltime.year * 10000000000LL + ltime.month * 100000000LL +
@@ -5177,7 +5193,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr)
   to= (char*) val_buffer->ptr();
   val_buffer->length(field_length);
 
-  if (get_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_date(&ltime, Datetime::Options(TIME_NO_ZERO_DATE, get_thd())))
   {				      /* Zero time is "000000" */
     val_ptr->set(zero_timestamp, field_length, &my_charset_numeric);
     return val_ptr;
@@ -5407,7 +5423,7 @@ my_time_t Field_timestamp_hires::get_timestamp(const uchar *pos,
 double Field_timestamp_with_dec::val_real(void)
 {
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_date(&ltime, Datetime::Options(TIME_NO_ZERO_DATE, get_thd())))
     return 0;
   
   return ltime.year * 1e10 + ltime.month * 1e8 +
@@ -5427,7 +5443,7 @@ int Field_timestamp::store_decimal(const my_decimal *d)
   int error;
   THD *thd= get_thd();
   ErrConvDecimal str(d);
-  Datetime dt(&error, d, sql_mode_for_timestamp(thd), decimals());
+  Datetime dt(thd, &error, d, Timestamp::DatetimeOptions(thd), decimals());
   return store_TIME_with_warning(thd, &dt, &str, error);
 }
 
@@ -5570,7 +5586,8 @@ int Field_datetime::store(const char *from, size_t len, CHARSET_INFO *cs)
 {
   MYSQL_TIME_STATUS st;
   ErrConvString str(from, len, cs);
-  Datetime dt(&st, from, len, cs, sql_mode_for_dates(get_thd()), decimals());
+  THD *thd= get_thd();
+  Datetime dt(thd, &st, from, len, cs, Datetime::Options(thd), decimals());
   return store_TIME_with_warning(&dt, &str, st.warnings);
 }
 
@@ -5578,7 +5595,8 @@ int Field_datetime::store(double nr)
 {
   int error;
   ErrConvDouble str(nr);
-  Datetime dt(&error, nr, sql_mode_for_dates(get_thd()), decimals());
+  THD *thd= get_thd();
+  Datetime dt(thd, &error, nr, Datetime::Options(thd), decimals());
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -5588,7 +5606,8 @@ int Field_datetime::store(longlong nr, bool unsigned_val)
   int error;
   Longlong_hybrid tmp(nr, unsigned_val);
   ErrConvInteger str(tmp);
-  Datetime dt(&error, tmp, sql_mode_for_dates(get_thd()));
+  THD *thd= get_thd();
+  Datetime dt(thd, &error, tmp, Datetime::Options(thd));
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -5597,7 +5616,7 @@ int Field_datetime::store_time_dec(const MYSQL_TIME *ltime, uint dec)
   int error;
   ErrConvTime str(ltime);
   THD *thd= get_thd();
-  Datetime dt(thd, &error, ltime, sql_mode_for_dates(thd), decimals());
+  Datetime dt(thd, &error, ltime, Datetime::Options(thd), decimals());
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -5606,7 +5625,8 @@ int Field_datetime::store_decimal(const my_decimal *d)
 {
   int error;
   ErrConvDecimal str(d);
-  Datetime tm(&error, d, sql_mode_for_dates(get_thd()), decimals());
+  THD *thd= get_thd();
+  Datetime tm(thd, &error, d, Datetime::Options(thd), decimals());
   return store_TIME_with_warning(&tm, &str, error);
 }
 
@@ -5617,7 +5637,7 @@ Field_temporal_with_date::validate_value_in_record(THD *thd,
 {
   DBUG_ASSERT(!is_null_in_record(record));
   MYSQL_TIME ltime;
-  return get_TIME(&ltime, ptr_in_record(record), sql_mode_for_dates(thd));
+  return get_TIME(&ltime, ptr_in_record(record), Datetime::Options(thd));
 }
 
 
@@ -5657,7 +5677,8 @@ Item *Field_temporal::get_equal_const_item_datetime(THD *thd,
          const_item->field_type() != MYSQL_TYPE_TIMESTAMP) ||
         const_item->decimals != decimals())
     {
-      Datetime dt(thd, const_item, date_mode_t(0));
+      Datetime::Options opt(TIME_CONV_NONE, thd);
+      Datetime dt(thd, const_item, opt, decimals());
       if (!dt.is_valid_datetime())
         return NULL;
       /*
@@ -5672,7 +5693,7 @@ Item *Field_temporal::get_equal_const_item_datetime(THD *thd,
   case ANY_SUBST:
     if (!is_temporal_type_with_date(const_item->field_type()))
     {
-      Datetime dt(thd, const_item, Datetime::comparison_flags_for_get_date());
+      Datetime dt(thd, const_item, Datetime::Options_cmp(thd));
       if (!dt.is_valid_datetime())
         return NULL;
       return new (thd->mem_root)
@@ -5724,7 +5745,17 @@ int Field_time::store(const char *from,size_t len,CHARSET_INFO *cs)
   ErrConvString str(from, len, cs);
   MYSQL_TIME_STATUS st;
   THD *thd= get_thd();
-  Time tm(thd, &st, from, len, cs, sql_mode_for_dates(thd), decimals());
+  /*
+    Unlike number-to-time conversion, we need to additionally pass
+    MODE_NO_ZERO_DATE here (if it presents in the current sql_mode):
+      SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
+      INSERT INTO t1 VALUES ('0000-00-00 00:00:00');  -- error
+      INSERT INTO t1 VALUES (0);                      -- ok
+    In the first INSERT we have a zero date.
+    In the second INSERT we don't have a zero date (it is just a zero time).
+  */
+  Time::Options opt(sql_mode_for_dates(thd), thd);
+  Time tm(thd, &st, from, len, cs, opt, decimals());
   return store_TIME_with_warning(&tm, &str, st.warnings);
 }
 
@@ -5733,7 +5764,7 @@ int Field_time::store_time_dec(const MYSQL_TIME *ltime, uint dec)
 {
   ErrConvTime str(ltime);
   int warn;
-  Time tm(&warn, ltime, curdays, decimals());
+  Time tm(&warn, ltime, curdays, Time::Options(get_thd()), decimals());
   return store_TIME_with_warning(&tm, &str, warn);
 }
 
@@ -5742,7 +5773,7 @@ int Field_time::store(double nr)
 {
   ErrConvDouble str(nr);
   int was_cut;
-  Time tm(get_thd(), &was_cut, nr, Time::Options(), decimals());
+  Time tm(get_thd(), &was_cut, nr, Time::Options(get_thd()), decimals());
   return store_TIME_with_warning(&tm, &str, was_cut);
 }
 
@@ -5752,8 +5783,14 @@ int Field_time::store(longlong nr, bool unsigned_val)
   Longlong_hybrid tmp(nr, unsigned_val);
   ErrConvInteger str(tmp);
   int was_cut;
-  // Need fractional digit truncation if nr overflows to '838:59:59.999999'
-  Time tm(get_thd(), &was_cut, tmp, Time::Options(), decimals());
+  THD *thd= get_thd();
+  /*
+    Need fractional digit truncation if nr overflows to '838:59:59.999999'.
+    The constructor used below will always truncate (never round).
+    We don't need to care to overwrite the default session rounding mode
+    from HALF_UP to TRUNCATE.
+  */
+  Time tm(thd, &was_cut, tmp, Time::Options(thd), decimals());
   return store_TIME_with_warning(&tm, &str, was_cut);
 }
 
@@ -5805,7 +5842,7 @@ String *Field_time::val_str(String *str,
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
   MYSQL_TIME ltime;
-  get_date(&ltime, TIME_TIME_ONLY);
+  get_date(&ltime, Datetime::Options(TIME_TIME_ONLY, get_thd()));
   str->alloc(field_length + 1);
   str->length(my_time_to_str(&ltime, const_cast<char*>(str->ptr()), decimals()));
   str->set_charset(&my_charset_numeric);
@@ -5815,7 +5852,8 @@ String *Field_time::val_str(String *str,
 
 bool Field_time::check_zero_in_date_with_warn(date_mode_t fuzzydate)
 {
-  if (!(fuzzydate & TIME_TIME_ONLY) && (fuzzydate & TIME_NO_ZERO_IN_DATE))
+  date_conv_mode_t tmp= date_conv_mode_t(fuzzydate);
+  if (!(tmp & TIME_TIME_ONLY) && (tmp & TIME_NO_ZERO_IN_DATE))
   {
     THD *thd= get_thd();
     push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
@@ -5860,7 +5898,7 @@ bool Field_time::get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate)
 bool Field_time::send_binary(Protocol *protocol)
 {
   MYSQL_TIME ltime;
-  get_date(&ltime, TIME_TIME_ONLY);
+  get_date(&ltime, Time::Options(TIME_TIME_ONLY, get_thd()));
   return protocol->store_time(&ltime, decimals());
 }
 
@@ -5911,7 +5949,7 @@ int Field_time::store_decimal(const my_decimal *d)
 {
   ErrConvDecimal str(d);
   int was_cut;
-  Time tm(get_thd(), &was_cut, d, Time::Options(), decimals());
+  Time tm(get_thd(), &was_cut, d, Time::Options(get_thd()), decimals());
   return store_TIME_with_warning(&tm, &str, was_cut);
 }
 
@@ -5971,8 +6009,7 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
     if (const_item->field_type() != MYSQL_TYPE_TIME)
     {
       // Get the value of const_item with conversion from DATETIME to TIME
-      Time tm(get_thd(), const_item,
-              Time::Options(Time::comparison_flags_for_get_date(), mode));
+      Time tm(get_thd(), const_item, Time::Options_cmp(thd, mode));
       if (!tm.is_valid_time())
         return NULL;
       /*
@@ -5996,10 +6033,6 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
     if (const_item->field_type() != MYSQL_TYPE_TIME ||
         const_item->decimals != decimals())
     {
-      Time tm(get_thd(), const_item,
-              Time::Options(TIME_TIME_ONLY, mode));
-      if (!tm.is_valid_time())
-        return NULL;
       /*
         Note, the value returned in "ltime" can have more fractional
         digits that decimals(). The Item_time_literal constructor will
@@ -6014,6 +6047,10 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
         The optimized WHERE will return with "Impossible WHERE", without
         having to do the full table scan.
       */
+      Time tm(thd, const_item, Time::Options(TIME_TIME_ONLY, thd, mode),
+              decimals());
+      if (!tm.is_valid_time())
+        return NULL;
       return new (thd->mem_root) Item_time_literal(thd, tm.get_mysql_time(),
                                                    decimals());
     }
@@ -6027,7 +6064,7 @@ longlong Field_time_with_dec::val_int(void)
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
   MYSQL_TIME ltime;
-  get_date(&ltime, TIME_TIME_ONLY);
+  get_date(&ltime, Time::Options(TIME_TIME_ONLY, get_thd()));
   longlong val= TIME_to_ulonglong_time(&ltime);
   return ltime.neg ? -val : val;
 }
@@ -6036,7 +6073,7 @@ double Field_time_with_dec::val_real(void)
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
   MYSQL_TIME ltime;
-  get_date(&ltime, TIME_TIME_ONLY);
+  get_date(&ltime, Time::Options(TIME_TIME_ONLY, get_thd()));
   return TIME_to_double(&ltime);
 }
 
@@ -6268,7 +6305,8 @@ int Field_date_common::store(const char *from, size_t len, CHARSET_INFO *cs)
 {
   MYSQL_TIME_STATUS st;
   ErrConvString str(from, len, cs);
-  Datetime dt(&st, from, len, cs, sql_mode_for_dates(get_thd()));
+  THD *thd= get_thd();
+  Datetime dt(thd, &st, from, len, cs, Date::Options(thd), 0);
   return store_TIME_with_warning(&dt, &str, st.warnings);
 }
 
@@ -6276,7 +6314,8 @@ int Field_date_common::store(double nr)
 {
   int error;
   ErrConvDouble str(nr);
-  Datetime dt(&error, nr, sql_mode_for_dates(get_thd()));
+  THD *thd= get_thd();
+  Datetime dt(thd, &error, nr, Date::Options(thd), 0);
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -6285,7 +6324,8 @@ int Field_date_common::store(longlong nr, bool unsigned_val)
   int error;
   Longlong_hybrid tmp(nr, unsigned_val);
   ErrConvInteger str(tmp);
-  Datetime dt(&error, tmp, sql_mode_for_dates(get_thd()));
+  THD *thd= get_thd();
+  Datetime dt(thd, &error, tmp, Date::Options(thd));
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -6294,7 +6334,7 @@ int Field_date_common::store_time_dec(const MYSQL_TIME *ltime, uint dec)
   int error;
   ErrConvTime str(ltime);
   THD *thd= get_thd();
-  Datetime dt(thd, &error, ltime, sql_mode_for_dates(thd));
+  Datetime dt(thd, &error, ltime, Date::Options(thd), 0);
   return store_TIME_with_warning(&dt, &str, error);
 }
 
@@ -6302,7 +6342,8 @@ int Field_date_common::store_decimal(const my_decimal *d)
 {
   int error;
   ErrConvDecimal str(d);
-  Datetime tm(&error, d, sql_mode_for_dates(get_thd()));
+  THD *thd= get_thd();
+  Datetime tm(thd, &error, d, Date::Options(thd), 0);
   return store_TIME_with_warning(&tm, &str, error);
 }
 
@@ -6512,8 +6553,14 @@ Item *Field_newdate::get_equal_const_item(THD *thd, const Context &ctx,
   case ANY_SUBST:
     if (!is_temporal_type_with_date(const_item->field_type()))
     {
-      // Get the value of const_item with conversion from TIME to DATETIME
-      Datetime dt(thd, const_item, Datetime::comparison_flags_for_get_date());
+      /*
+        DATE is compared to DATETIME-alike non-temporal values
+        (such as VARCHAR, DECIMAL) as DATETIME, e.g.:
+          WHERE date_column=20010101235959.0000009
+        So here we convert the constant to DATETIME normally.
+        In case if TIME_ROUND_FRACTIONAL is enabled, nanoseconds will round.
+      */
+      Datetime dt(thd, const_item, Datetime::Options_cmp(thd));
       if (!dt.is_valid_datetime())
         return NULL;
       /*
@@ -6540,10 +6587,17 @@ Item *Field_newdate::get_equal_const_item(THD *thd, const Context &ctx,
   case IDENTITY_SUBST:
     if (const_item->field_type() != MYSQL_TYPE_DATE)
     {
-      Date d(thd, const_item, date_mode_t(0));
-      if (!d.is_valid_date())
+      /*
+        DATE is compared to non-temporal as DATETIME.
+        We need to convert to DATETIME first, taking into account the
+        current session rounding mode (even though this is IDENTITY_SUBSTS!),
+        then convert the result to DATE.
+      */
+      Datetime dt(thd, const_item, Datetime::Options(TIME_CONV_NONE, thd));
+      if (!dt.is_valid_datetime())
         return NULL;
-      return new (thd->mem_root) Item_date_literal(thd, d.get_mysql_time());
+      return new (thd->mem_root)
+        Item_date_literal(thd, Date(&dt).get_mysql_time());
     }
     break;
   }
@@ -6693,7 +6747,8 @@ int Field_datetime::set_time()
 {
   THD *thd= table->in_use;
   set_notnull();
-  store_datetime(Datetime(thd, thd->query_start_timeval(), decimals()));
+  // Here we always truncate (not round), no matter what sql_mode is
+  store_datetime(Datetime(thd, thd->query_start_timeval()).trunc(decimals()));
   return 0;
 }
 
diff --git a/sql/field.h b/sql/field.h
index 7c5552ec13c..cca3b186668 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1346,7 +1346,6 @@ public:
   void copy_from_tmp(int offset);
   uint fill_cache_field(struct st_cache_field *copy);
   virtual bool get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate);
-  bool get_time(MYSQL_TIME *ltime) { return get_date(ltime, TIME_TIME_ONLY); }
   virtual TYPELIB *get_typelib() const { return NULL; }
   virtual CHARSET_INFO *charset(void) const { return &my_charset_bin; }
   virtual CHARSET_INFO *charset_for_protocol(void) const
@@ -2643,7 +2642,8 @@ public:
   int save_in_field(Field *to)
   {
     MYSQL_TIME ltime;
-    if (get_date(&ltime, date_mode_t(0)))
+    // For temporal types no truncation needed. Rounding mode is not important.
+    if (get_date(&ltime, TIME_CONV_NONE | TIME_FRAC_NONE))
       return to->reset();
     return to->store_time_dec(&ltime, decimals());
   }
@@ -2721,7 +2721,6 @@ public:
 
 class Field_timestamp :public Field_temporal {
 protected:
-  date_mode_t sql_mode_for_timestamp(THD *thd) const;
   int store_TIME_with_warning(THD *, const Datetime *,
                               const ErrConv *, int warn);
   virtual void store_TIMEVAL(const timeval &tv)
@@ -2771,10 +2770,15 @@ public:
   {
     return get_timestamp(ptr, sec_part);
   }
-  // This is used by storage/perfschema
-  void store_TIME(my_time_t timestamp, ulong sec_part)
+  /*
+    This method is used by storage/perfschema and
+    Item_func_now_local::save_in_field().
+  */
+  void store_TIME(my_time_t ts, ulong sec_part)
   {
-    store_TIMESTAMP(Timestamp(timestamp, sec_part).trunc(decimals()));
+    int warn;
+    time_round_mode_t mode= Datetime::default_round_mode(get_thd());
+    store_TIMESTAMP(Timestamp(ts, sec_part).round(decimals(), mode, &warn));
   }
   bool get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate);
   uchar *pack(uchar *to, const uchar *from,
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index 7a064f64570..2f56be60dd6 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -438,19 +438,19 @@ void Field::do_field_temporal(Copy_field *copy, date_mode_t fuzzydate)
 
 void Field::do_field_datetime(Copy_field *copy)
 {
-  return do_field_temporal(copy, date_mode_t(0));
+  return do_field_temporal(copy, Datetime::Options(TIME_CONV_NONE, current_thd));
 }
 
 
 void Field::do_field_date(Copy_field *copy)
 {
-  return do_field_temporal(copy, date_mode_t(0));
+  return do_field_temporal(copy, Date::Options(TIME_CONV_NONE));
 }
 
 
 void Field_time::do_field_time(Copy_field *copy)
 {
-  return do_field_temporal(copy, TIME_TIME_ONLY);
+  return do_field_temporal(copy, Time::Options(current_thd));
 }
 
 
diff --git a/sql/filesort.cc b/sql/filesort.cc
index cbe79967647..d0cb77ac63d 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -1051,7 +1051,10 @@ Type_handler_temporal_result::make_sort_key(uchar *to, Item *item,
                                             Sort_param *param) const
 {
   MYSQL_TIME buf;
-  if (item->get_date_result(current_thd, &buf, TIME_INVALID_DATES))
+  // This is a temporal type. No nanoseconds. Rounding mode is not important.
+  DBUG_ASSERT(item->cmp_type() == TIME_RESULT);
+  static const Temporal::Options opt(TIME_INVALID_DATES, TIME_FRAC_NONE);
+  if (item->get_date_result(current_thd, &buf, opt))
   {
     DBUG_ASSERT(item->maybe_null);
     DBUG_ASSERT(item->null_value);
diff --git a/sql/item.cc b/sql/item.cc
index 26a8aa44b80..99f801bb292 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -118,12 +118,12 @@ void Item::push_note_converted_to_positive_complement(THD *thd)
 longlong Item::val_datetime_packed_result(THD *thd)
 {
   MYSQL_TIME ltime, tmp;
-  if (get_date_result(thd, &ltime, Datetime::comparison_flags_for_get_date()))
+  if (get_date_result(thd, &ltime, Datetime::Options_cmp(thd)))
     return 0;
   if (ltime.time_type != MYSQL_TIMESTAMP_TIME)
     return pack_time(&ltime);
-  if ((null_value= time_to_datetime_with_warn(thd, &ltime,
-                                              &tmp, date_mode_t(0))))
+  if ((null_value= time_to_datetime_with_warn(thd, &ltime, &tmp,
+                                              TIME_CONV_NONE)))
     return 0;
   return pack_time(&tmp);
 }
@@ -305,7 +305,7 @@ int Item::save_date_in_field(Field *field, bool no_conversions)
 {
   MYSQL_TIME ltime;
   THD *thd= field->table->in_use;
-  if (get_date(thd, &ltime, sql_mode_for_dates(thd)))
+  if (get_date(thd, &ltime, Datetime::Options(thd)))
     return set_field_to_null_with_conversions(field, no_conversions);
   field->set_notnull();
   return field->store_time_dec(&ltime, decimals);
@@ -9732,7 +9732,8 @@ bool Item_cache_temporal::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzz
 int Item_cache_temporal::save_in_field(Field *field, bool no_conversions)
 {
   MYSQL_TIME ltime;
-  if (get_date(field->get_thd(), &ltime, date_mode_t(0)))
+  // This is a temporal type. No nanoseconds, so round mode is not important.
+  if (get_date(field->get_thd(), &ltime, TIME_CONV_NONE | TIME_FRAC_NONE))
     return set_field_to_null_with_conversions(field, no_conversions);
   field->set_notnull();
   int error= field->store_time_dec(&ltime, decimals);
diff --git a/sql/item.h b/sql/item.h
index 2c9395ff89d..2ee2eabddac 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1636,28 +1636,28 @@ public:
   bool get_date_from_real(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate);
   bool get_date_from_string(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate);
   bool get_time(THD *thd, MYSQL_TIME *ltime)
-  { return get_date(thd, ltime, Time::flags_for_get_date()); }
+  { return get_date(thd, ltime, Time::Options(thd)); }
   // Get a DATE or DATETIME value in numeric packed format for comparison
   virtual longlong val_datetime_packed(THD *thd)
   {
-    date_mode_t fuzzydate= Datetime::comparison_flags_for_get_date();
-    return Datetime(current_thd, this, fuzzydate).to_packed();
+    return Datetime(thd, this, Datetime::Options_cmp(thd)).to_packed();
   }
   // Get a TIME value in numeric packed format for comparison
   virtual longlong val_time_packed(THD *thd)
   {
-    return Time(thd, this, Time::comparison_flags_for_get_date()).to_packed();
+    return Time(thd, this, Time::Options_cmp(thd)).to_packed();
   }
   longlong val_datetime_packed_result(THD *thd);
   longlong val_time_packed_result(THD *thd)
   {
     MYSQL_TIME ltime;
-    date_mode_t fuzzydate= Time::comparison_flags_for_get_date();
-    return get_date_result(thd, &ltime, fuzzydate) ? 0 : pack_time(&ltime);
+    return get_date_result(thd, &ltime, Time::Options_cmp(thd)) ? 0 :
+           pack_time(&ltime);
   }
 
   virtual bool get_date_result(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
   { return get_date(thd, ltime,fuzzydate); }
+
   /*
     The method allows to determine nullness of a complex expression 
     without fully evaluating it, instead of calling val/result*() then 
@@ -6441,8 +6441,8 @@ public:
   Item *make_literal(THD *);
   longlong val_datetime_packed(THD *thd)
   {
-    date_mode_t fuzzy= Datetime::comparison_flags_for_get_date();
-    return has_value() ? Datetime(thd, this, fuzzy).to_packed() : 0;
+    Datetime::Options_cmp opt(thd);
+    return has_value() ? Datetime(thd, this, opt).to_packed() : 0;
   }
   longlong val_time_packed(THD *thd)
   {
@@ -6481,7 +6481,7 @@ public:
   }
   longlong val_time_packed(THD *thd)
   {
-    return Time(thd, this, Time::comparison_flags_for_get_date()).to_packed();
+    return Time(thd, this, Time::Options_cmp(thd)).to_packed();
   }
   longlong val_int()
   {
@@ -6516,7 +6516,7 @@ public:
   }
   longlong val_time_packed(THD *thd)
   {
-    return Time(thd, this, Time::comparison_flags_for_get_date()).to_packed();
+    return Time(thd, this, Time::Options_cmp(thd)).to_packed();
   }
   longlong val_int() { return has_value() ? Date(this).to_longlong() : 0; }
   double val_real() { return has_value() ? Date(this).to_double() : 0; }
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 9488391f602..15f1f917b35 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2312,7 +2312,8 @@ bool Item_func_ifnull::date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydat
   DBUG_ASSERT(fixed == 1);
   for (uint i= 0; i < 2; i++)
   {
-    Datetime dt(thd, args[i], fuzzydate & ~TIME_FUZZY_DATES);
+    Datetime_truncation_not_needed dt(thd, args[i],
+                                      fuzzydate & ~TIME_FUZZY_DATES);
     if (!(dt.copy_to_mysql_time(ltime, mysql_timestamp_type())))
       return (null_value= false);
   }
@@ -2812,7 +2813,7 @@ Item_func_nullif::date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
   DBUG_ASSERT(fixed == 1);
   if (!compare())
     return (null_value= true);
-  Datetime dt(thd, args[2], fuzzydate);
+  Datetime_truncation_not_needed dt(thd, args[2], fuzzydate);
   return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
 }
 
@@ -2987,7 +2988,7 @@ bool Item_func_case::date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
   Item *item= find_item();
   if (!item)
     return (null_value= true);
-  Datetime dt(thd, item, fuzzydate);
+  Datetime_truncation_not_needed dt(thd, item, fuzzydate);
   return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
 }
 
@@ -3339,7 +3340,8 @@ bool Item_func_coalesce::date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzyd
   DBUG_ASSERT(fixed == 1);
   for (uint i= 0; i < arg_count; i++)
   {
-    Datetime dt(thd, args[i], fuzzydate & ~TIME_FUZZY_DATES);
+    Datetime_truncation_not_needed dt(thd, args[i],
+                                      fuzzydate & ~TIME_FUZZY_DATES);
     if (!dt.copy_to_mysql_time(ltime, mysql_timestamp_type()))
       return (null_value= false);
   }
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index cf210a71433..213f666dd74 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1127,7 +1127,7 @@ public:
 
   bool date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
   {
-    Datetime dt(thd, find_item(), fuzzydate);
+    Datetime_truncation_not_needed dt(thd, find_item(), fuzzydate);
     return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
   }
   bool time_op(THD *thd, MYSQL_TIME *ltime)
diff --git a/sql/item_func.cc b/sql/item_func.cc
index c176a7e43a7..ee2367a87b0 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2610,13 +2610,13 @@ bool Item_func_min_max::get_time_native(THD *thd, MYSQL_TIME *ltime)
 {
   DBUG_ASSERT(fixed == 1);
 
-  Time value(thd, args[0], Time::Options(), decimals);
+  Time value(thd, args[0], Time::Options(thd), decimals);
   if (!value.is_valid_time())
     return (null_value= true);
 
   for (uint i= 1; i < arg_count ; i++)
   {
-    Time tmp(thd, args[i], Time::Options(), decimals);
+    Time tmp(thd, args[i], Time::Options(thd), decimals);
     if (!tmp.is_valid_time())
       return (null_value= true);
 
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 6c82c580858..6f2af72dabf 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -4544,7 +4544,7 @@ bool Item_func_dyncol_create::prepare_arguments(THD *thd, bool force_names_arg)
     case DYN_COL_DATETIME:
     case DYN_COL_DATE:
       args[valpos]->get_date(thd, &vals[i].x.time_value,
-                             sql_mode_for_dates(thd));
+                             Datetime::Options(thd));
       break;
     case DYN_COL_TIME:
       args[valpos]->get_time(thd, &vals[i].x.time_value);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index df4dc8cb96d..1b728f561b7 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -131,7 +131,7 @@ static bool extract_date_time(THD *thd, DATE_TIME_FORMAT *format,
                               timestamp_type cached_timestamp_type,
                               const char **sub_pattern_end,
                               const char *date_time_type,
-                              date_mode_t fuzzydate)
+                              date_conv_mode_t fuzzydate)
 {
   int weekday= 0, yearday= 0, daypart= 0;
   int week_number= -1;
@@ -808,8 +808,9 @@ longlong Item_func_period_diff::val_int()
 longlong Item_func_to_days::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
-  return (null_value= !d.is_valid_date()) ? 0 : d.daynr();
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.daynr();
 }
 
 
@@ -817,14 +818,15 @@ longlong Item_func_to_seconds::val_int_endpoint(bool left_endp,
                                                 bool *incl_endp)
 {
   DBUG_ASSERT(fixed == 1);
-  Datetime dt(current_thd, args[0], TIME_FUZZY_DATES);
+  // val_int_endpoint() is called only if args[0] is a temporal Item_field
+  Datetime_from_temporal dt(current_thd, args[0], TIME_FUZZY_DATES);
   if ((null_value= !dt.is_valid_datetime()))
   {
     /* got NULL, leave the incl_endp intact */
     return LONGLONG_MIN;
   }
   /* Set to NULL if invalid date, but keep the value */
-  null_value= dt.check_date(TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE);
+  null_value= dt.check_date(TIME_NO_ZEROS);
   /*
     Even if the evaluation return NULL, seconds is useful for pruning
   */
@@ -835,7 +837,11 @@ longlong Item_func_to_seconds::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   THD *thd= current_thd;
-  Datetime dt(thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
+  /*
+    Unlike val_int_endpoint(), we cannot use Datetime_from_temporal here.
+    The argument can be of a non-temporal data type.
+  */
+  Datetime dt(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
   return (null_value= !dt.is_valid_datetime()) ? 0 : dt.to_seconds();
 }
 
@@ -880,7 +886,8 @@ enum_monotonicity_info Item_func_to_seconds::get_monotonicity_info() const
 longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
 {
   DBUG_ASSERT(fixed == 1);
-  Datetime dt(current_thd, args[0], date_mode_t(0));
+  // val_int_endpoint() is only called if args[0] is a temporal Item_field
+  Datetime_from_temporal dt(current_thd, args[0], TIME_CONV_NONE);
   longlong res;
   if ((null_value= !dt.is_valid_datetime()))
   {
@@ -889,7 +896,7 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
   }
   res= (longlong) dt.daynr();
   /* Set to NULL if invalid date, but keep the value */
-  null_value= dt.check_date(TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE);
+  null_value= dt.check_date(TIME_NO_ZEROS);
   if (null_value)
   {
     /*
@@ -933,22 +940,25 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
 longlong Item_func_dayofyear::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE);
-  return (null_value= !d.is_valid_date()) ? 0 : d.dayofyear();
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.dayofyear();
 }
 
 longlong Item_func_dayofmonth::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], date_mode_t(0));
-  return (null_value= !d.is_valid_date()) ? 0 : d.get_mysql_time()->day;
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.get_mysql_time()->day;
 }
 
 longlong Item_func_month::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], date_mode_t(0));
-  return (null_value= !d.is_valid_date()) ? 0 : d.get_mysql_time()->month;
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.get_mysql_time()->month;
 }
 
 
@@ -970,9 +980,9 @@ String* Item_func_monthname::val_str(String* str)
   DBUG_ASSERT(fixed == 1);
   const char *month_name;
   uint err;
-  Date d(current_thd, args[0], date_mode_t(0));
-
-  if ((null_value= (!d.is_valid_date() || !d.get_mysql_time()->month)))
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
+  if ((null_value= (!d.is_valid_datetime() || !d.get_mysql_time()->month)))
     return (String *) 0;
 
   month_name= locale->month_names->type_names[d.get_mysql_time()->month - 1];
@@ -989,21 +999,24 @@ String* Item_func_monthname::val_str(String* str)
 longlong Item_func_quarter::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], date_mode_t(0));
-  return (null_value= !d.is_valid_date()) ? 0 : d.quarter();
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.quarter();
 }
 
 longlong Item_func_hour::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   return (null_value= !tm.is_valid_time()) ? 0 : tm.get_mysql_time()->hour;
 }
 
 longlong Item_func_minute::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   return (null_value= !tm.is_valid_time()) ? 0 : tm.get_mysql_time()->minute;
 }
 
@@ -1013,7 +1026,8 @@ longlong Item_func_minute::val_int()
 longlong Item_func_second::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   return (null_value= !tm.is_valid_time()) ? 0 : tm.get_mysql_time()->second;
 }
 
@@ -1062,8 +1076,8 @@ longlong Item_func_week::val_int()
   DBUG_ASSERT(fixed == 1);
   uint week_format;
   THD *thd= current_thd;
-  Date d(thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
-  if ((null_value= !d.is_valid_date()))
+  Datetime d(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
+  if ((null_value= !d.is_valid_datetime()))
     return 0;
   if (arg_count > 1)
     week_format= (uint)args[1]->val_int();
@@ -1076,8 +1090,9 @@ longlong Item_func_week::val_int()
 longlong Item_func_yearweek::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
-  return (null_value= !d.is_valid_date()) ? 0 :
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 :
          d.yearweek((week_mode((uint) args[1]->val_int()) | WEEK_YEAR));
 }
 
@@ -1085,8 +1100,9 @@ longlong Item_func_yearweek::val_int()
 longlong Item_func_weekday::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
-  return ((null_value= !d.is_valid_date())) ? 0 :
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_NO_ZEROS, thd));
+  return ((null_value= !d.is_valid_datetime())) ? 0 :
          calc_weekday(d.daynr(), odbc_type) + MY_TEST(odbc_type);
 }
 
@@ -1123,8 +1139,9 @@ String* Item_func_dayname::val_str(String* str)
 longlong Item_func_year::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Date d(current_thd, args[0], date_mode_t(0));
-  return (null_value= !d.is_valid_date()) ? 0 : d.get_mysql_time()->year;
+  THD *thd= current_thd;
+  Datetime d(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
+  return (null_value= !d.is_valid_datetime()) ? 0 : d.get_mysql_time()->year;
 }
 
 
@@ -1155,7 +1172,8 @@ enum_monotonicity_info Item_func_year::get_monotonicity_info() const
 longlong Item_func_year::val_int_endpoint(bool left_endp, bool *incl_endp)
 {
   DBUG_ASSERT(fixed == 1);
-  Datetime dt(current_thd, args[0], date_mode_t(0));
+  // val_int_endpoint() is cally only if args[0] is a temporal Item_field
+  Datetime_from_temporal dt(current_thd, args[0], TIME_CONV_NONE);
   if ((null_value= !dt.is_valid_datetime()))
   {
     /* got NULL, leave the incl_endp intact */
@@ -1200,7 +1218,7 @@ bool Item_func_unix_timestamp::get_timestamp_value(my_time_t *seconds,
   }
 
   THD *thd= current_thd;
-  Datetime dt(thd, args[0], TIME_NO_ZERO_IN_DATE);
+  Datetime dt(thd, args[0], Datetime::Options(TIME_NO_ZERO_IN_DATE, thd));
   if ((null_value= !dt.is_valid_datetime()))
     return true;
 
@@ -1264,7 +1282,8 @@ longlong Item_func_unix_timestamp::val_int_endpoint(bool left_endp, bool *incl_e
 longlong Item_func_time_to_sec::int_op()
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   return ((null_value= !tm.is_valid_time())) ? 0 : tm.to_seconds();
 }
 
@@ -1272,7 +1291,8 @@ longlong Item_func_time_to_sec::int_op()
 my_decimal *Item_func_time_to_sec::decimal_op(my_decimal* buf)
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   if ((null_value= !tm.is_valid_time()))
     return 0;
   const MYSQL_TIME *ltime= tm.get_mysql_time();
@@ -1621,9 +1641,8 @@ int Item_func_now_local::save_in_field(Field *field, bool no_conversions)
   {
     THD *thd= field->get_thd();
     my_time_t ts= thd->query_start();
-    uint dec= MY_MIN(decimals, field->decimals());
-    ulong sec_part= dec ? thd->query_start_sec_part() : 0;
-    sec_part-= my_time_fraction_remainder(sec_part, dec);
+    ulong sec_part= decimals ? thd->query_start_sec_part() : 0;
+    sec_part-= my_time_fraction_remainder(sec_part, decimals);
     field->set_notnull();
     ((Field_timestamp*)field)->store_TIME(ts, sec_part);
     return 0;
@@ -1698,9 +1717,10 @@ bool Item_func_sysdate_local::get_date(THD *thd, MYSQL_TIME *res,
 bool Item_func_sec_to_time::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
 {
   DBUG_ASSERT(fixed == 1);
-  VSec6 sec(thd, args[0], "seconds", LONGLONG_MAX);
+  VSec9 sec(thd, args[0], "seconds", LONGLONG_MAX);
   if ((null_value= sec.is_null()))
     return true;
+  sec.round(decimals, thd->temporal_round_mode());
   if (sec.sec_to_time(ltime, decimals) && !sec.truncated())
     sec.make_truncated_warning(thd, "seconds");
   return false;
@@ -1858,10 +1878,10 @@ String *Item_func_date_format::val_str(String *str)
   uint size;
   const MY_LOCALE *lc= 0;
   DBUG_ASSERT(fixed == 1);
-  
-  if ((null_value= args[0]->get_date(current_thd, &l_time,
-                                     is_time_format ? TIME_TIME_ONLY :
-                                                      date_mode_t(0))))
+  date_conv_mode_t mode= is_time_format ? TIME_TIME_ONLY : TIME_CONV_NONE;
+  THD *thd= current_thd;
+  if ((null_value= args[0]->get_date(thd, &l_time,
+                                     Temporal::Options(mode, thd))))
     return 0;
   
   if (!(format = args[1]->val_str(str)) || !format->length())
@@ -1918,12 +1938,16 @@ bool Item_func_from_unixtime::get_date(THD *thd, MYSQL_TIME *ltime,
   bzero((char *)ltime, sizeof(*ltime));
   ltime->time_type= MYSQL_TIMESTAMP_TIME;
 
-  VSec6 sec(thd, args[0], "unixtime", TIMESTAMP_MAX_VALUE);
+  VSec9 sec(thd, args[0], "unixtime", TIMESTAMP_MAX_VALUE);
   DBUG_ASSERT(sec.sec() <= TIMESTAMP_MAX_VALUE);
 
   if (sec.is_null() || sec.truncated() || sec.neg())
     return (null_value= 1);
 
+  sec.round(MY_MIN(decimals, TIME_SECOND_PART_DIGITS), thd->temporal_round_mode());
+  if (sec.sec() > TIMESTAMP_MAX_VALUE)
+    return (null_value= true); // Went out of range after rounding
+
   tz->gmt_sec_to_TIME(ltime, (my_time_t) sec.sec());
   ltime->second_part= sec.usec();
 
@@ -1952,7 +1976,8 @@ bool Item_func_convert_tz::get_date(THD *thd, MYSQL_TIME *ltime,
   if ((null_value= (from_tz == 0 || to_tz == 0)))
     return true;
 
-  Datetime *dt= new(ltime) Datetime(thd, args[0], TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE);
+  Datetime::Options opt(TIME_NO_ZEROS, thd);
+  Datetime *dt= new(ltime) Datetime(thd, args[0], opt);
   if ((null_value= !dt->is_valid_datetime()))
     return true;
 
@@ -2133,7 +2158,8 @@ uint Extract_source::week(THD *thd) const
 longlong Item_extract::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Extract_source dt(current_thd, args[0], m_date_mode);
+  THD *thd= current_thd;
+  Extract_source dt(thd, args[0], m_date_mode);
   if ((null_value= !dt.is_valid_extract_source()))
     return 0;
   switch (int_type) {
@@ -2141,7 +2167,7 @@ longlong Item_extract::val_int()
   case INTERVAL_YEAR_MONTH:          return dt.year_month();
   case INTERVAL_QUARTER:             return dt.quarter();
   case INTERVAL_MONTH:               return dt.month();
-  case INTERVAL_WEEK:                return dt.week(current_thd);
+  case INTERVAL_WEEK:                return dt.week(thd);
   case INTERVAL_DAY:                 return dt.day();
   case INTERVAL_DAY_HOUR:            return dt.day_hour();
   case INTERVAL_DAY_MINUTE:          return dt.day_minute();
@@ -2420,7 +2446,7 @@ void Item_char_typecast::fix_length_and_dec_internal(CHARSET_INFO *from_cs)
 
 bool Item_time_typecast::get_date(THD *thd, MYSQL_TIME *to, date_mode_t mode)
 {
-  Time *tm= new(to) Time(thd, args[0], Time::Options_for_cast(mode),
+  Time *tm= new(to) Time(thd, args[0], Time::Options_for_cast(mode, thd),
                          MY_MIN(decimals, TIME_SECOND_PART_DIGITS));
   return (null_value= !tm->is_valid_time());
 }
@@ -2429,7 +2455,8 @@ bool Item_time_typecast::get_date(THD *thd, MYSQL_TIME *to, date_mode_t mode)
 bool Item_date_typecast::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
 {
   date_mode_t tmp= (fuzzydate | sql_mode_for_dates(thd)) & ~TIME_TIME_ONLY;
-  Date *d= new(ltime) Date(thd, args[0], tmp);
+  // Force truncation
+  Date *d= new(ltime) Date(thd, args[0], Date::Options(date_conv_mode_t(tmp)));
   return (null_value= !d->is_valid_date());
 }
 
@@ -2437,7 +2464,9 @@ bool Item_date_typecast::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzy
 bool Item_datetime_typecast::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
 {
   date_mode_t tmp= (fuzzydate | sql_mode_for_dates(thd)) & ~TIME_TIME_ONLY;
-  Datetime *dt= new(ltime) Datetime(thd, args[0], tmp,
+  // Force rounding if the current sql_mode says so
+  Datetime::Options opt(date_conv_mode_t(tmp), thd);
+  Datetime *dt= new(ltime) Datetime(thd, args[0], opt,
                                     MY_MIN(decimals, TIME_SECOND_PART_DIGITS));
   return (null_value= !dt->is_valid_datetime());
 }
@@ -2558,6 +2587,7 @@ bool Item_func_timediff::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzy
   return (null_value= adjust_time_range_with_warn(thd, ltime, decimals));
 }
 
+
 /**
   MAKETIME(h,m,s) is a time function that calculates a time value 
   from the total number of hours, minutes, and seconds.
@@ -2569,7 +2599,7 @@ bool Item_func_maketime::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzy
   DBUG_ASSERT(fixed == 1);
   Longlong_hybrid hour(args[0]->val_int(), args[0]->unsigned_flag);
   longlong minute= args[1]->val_int();
-  VSec6 sec(thd, args[2], "seconds", 59);
+  VSec9 sec(thd, args[2], "seconds", 59);
 
   DBUG_ASSERT(sec.sec() <= 59);
   if (args[0]->null_value || args[1]->null_value || sec.is_null() ||
@@ -2577,7 +2607,8 @@ bool Item_func_maketime::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzy
     return (null_value= 1);
 
   int warn;
-  new(ltime) Time(&warn, hour.neg(), hour.abs(), (uint) minute, sec);
+  new(ltime) Time(&warn, hour.neg(), hour.abs(), (uint) minute, sec,
+                  thd->temporal_round_mode(), decimals);
   if (warn)
   {
     // use check_time_range() to set ltime to the max value depending on dec
@@ -2607,7 +2638,8 @@ bool Item_func_maketime::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzy
 longlong Item_func_microsecond::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  Time tm(current_thd, args[0], Time::Options_for_cast());
+  THD *thd= current_thd;
+  Time tm(thd, args[0], Time::Options_for_cast(thd));
   return ((null_value= !tm.is_valid_time())) ?
          0 : tm.get_mysql_time()->second_part;
 }
@@ -2621,12 +2653,12 @@ longlong Item_func_timestamp_diff::val_int()
   long months= 0;
   int neg= 1;
   THD *thd= current_thd;
-  date_mode_t fuzzydate= TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE;
+  Datetime::Options opt(TIME_NO_ZEROS, thd);
 
   null_value= 0;
 
-  if (Datetime(thd, args[0], fuzzydate).copy_to_mysql_time(&ltime1) ||
-      Datetime(thd, args[1], fuzzydate).copy_to_mysql_time(&ltime2))
+  if (Datetime(thd, args[0], opt).copy_to_mysql_time(&ltime1) ||
+      Datetime(thd, args[1], opt).copy_to_mysql_time(&ltime2))
     goto null_date;
 
   if (calc_time_diff(&ltime2,&ltime1, 1,
@@ -2935,7 +2967,8 @@ bool Item_func_str_to_date::get_date_common(THD *thd, MYSQL_TIME *ltime,
   date_time_format.format.length= format->length();
   if (extract_date_time(thd, &date_time_format, val->ptr(), val->length(),
 			ltime, tstype, 0, "datetime",
-                        fuzzydate | sql_mode_for_dates(thd)))
+                        date_conv_mode_t(fuzzydate) |
+                        sql_mode_for_dates(thd)))
     return (null_value=1);
   return (null_value= 0);
 }
@@ -2943,8 +2976,10 @@ bool Item_func_str_to_date::get_date_common(THD *thd, MYSQL_TIME *ltime,
 
 bool Item_func_last_day::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
 {
-  Date *d= new(ltime) Date(thd, args[0], fuzzydate & ~TIME_TIME_ONLY);
-  if ((null_value= (!d->is_valid_date() || ltime->month == 0)))
+  Datetime::Options opt(date_conv_mode_t(fuzzydate & ~TIME_TIME_ONLY),
+                        time_round_mode_t(fuzzydate));
+  Datetime *d= new(ltime) Datetime(thd, args[0], opt);
+  if ((null_value= (!d->is_valid_datetime() || ltime->month == 0)))
     return true;
   uint month_idx= ltime->month-1;
   ltime->day= days_in_month[month_idx];
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 318bef22ad1..3824f87d12b 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -993,12 +993,12 @@ class Item_extract :public Item_int_func,
         EXTRACT(DAY FROM '-24:00:00') -> -1
     */
     set_handler(handler_by_length(max_length= length + 1/*sign*/, 11));
-    m_date_mode= TIME_INTERVAL_DAY;
+    m_date_mode= Temporal::Options(TIME_INTERVAL_DAY, current_thd);
   }
   void set_time_length(uint32 length)
   {
     set_handler(handler_by_length(max_length= length + 1/*sign*/, 11));
-    m_date_mode= TIME_INTERVAL_hhmmssff;
+    m_date_mode= Temporal::Options(TIME_INTERVAL_hhmmssff, current_thd);
   }
  public:
   const interval_type int_type; // keep it public
@@ -1117,7 +1117,7 @@ public:
   { }
   String *val_str(String *to)
   {
-    Interval_DDhhmmssff it(current_thd, args[0]);
+    Interval_DDhhmmssff it(current_thd, args[0], m_fsp);
     null_value= !it.is_valid_interval_DDhhmmssff();
     return it.to_string(to, m_fsp);
   }
@@ -1222,7 +1222,7 @@ public:
   }
   bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate)
   {
-    Datetime dt(thd, args[0], date_mode_t(0));
+    Datetime dt(thd, args[0], Datetime::Options(TIME_CONV_NONE, thd));
     if (!dt.is_valid_datetime())
       return null_value= true;
     Interval_DDhhmmssff it(thd, args[1]);
@@ -1461,7 +1461,8 @@ public:
   bool get_date(THD *thd, Item_handled_func *item,
                 MYSQL_TIME *to, date_mode_t fuzzy) const
   {
-    Datetime dt(thd, item->arguments()[0], date_mode_t(0));
+    Datetime::Options opt(TIME_CONV_NONE, thd);
+    Datetime dt(thd, item->arguments()[0], opt);
     if (!dt.is_valid_datetime() ||
          dt.check_date_with_warn(thd, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
       return (item->null_value= true);
@@ -1489,7 +1490,11 @@ public:
   bool get_date(THD *thd, Item_handled_func *item,
                 MYSQL_TIME *to, date_mode_t fuzzy) const
   {
-    Date d(thd, item->arguments()[0], date_mode_t(0));
+    /*
+      The first argument is known to be of the DATE data type (not DATETIME).
+      We don't need rounding here.
+    */
+    Date d(thd, item->arguments()[0], TIME_CONV_NONE);
     if (!d.is_valid_date() ||
          d.check_date_with_warn(thd, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
       return (item->null_value= true);
@@ -1542,10 +1547,10 @@ public:
   bool get_date(THD *thd, Item_handled_func *item,
                 MYSQL_TIME *to, date_mode_t fuzzy) const
   {
-    if (item->arguments()[0]->get_date(thd, to, date_mode_t(0)) ||
+    if (item->arguments()[0]->
+          get_date(thd, to, Datetime::Options(TIME_CONV_NONE, thd)) ||
         (to->time_type != MYSQL_TIMESTAMP_TIME &&
-         check_date_with_warn(thd, to, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE,
-                              MYSQL_TIMESTAMP_ERROR)))
+         check_date_with_warn(thd, to, TIME_NO_ZEROS, MYSQL_TIMESTAMP_ERROR)))
       return (item->null_value= true);
     return (item->null_value= add(thd, item->arguments()[1],
                                   int_type(item), sub(item), to));
@@ -1581,7 +1586,8 @@ public:
                 MYSQL_TIME *to, date_mode_t fuzzy) const
   {
     DBUG_ASSERT(item->is_fixed());
-    Datetime dt(thd, item->arguments()[0], date_mode_t(0));
+    Datetime::Options opt(TIME_CONV_NONE, thd);
+    Datetime dt(thd, item->arguments()[0], opt);
     if (!dt.is_valid_datetime())
       return item->null_value= true;
     Interval_DDhhmmssff it(thd, item->arguments()[1]);
@@ -1650,7 +1656,8 @@ public:
   {
     DBUG_ASSERT(item->is_fixed());
     // Detect a proper timestamp type based on the argument values
-    Temporal_hybrid l_time1(thd, item->arguments()[0], TIME_TIME_ONLY);
+    Temporal_hybrid l_time1(thd, item->arguments()[0],
+                            Temporal::Options(TIME_TIME_ONLY, thd));
     if (!l_time1.is_valid_temporal())
       return (item->null_value= true);
     Interval_DDhhmmssff l_time2(thd, item->arguments()[1]);
diff --git a/sql/item_vers.cc b/sql/item_vers.cc
index 6946ae0e1e5..c4bb734096f 100644
--- a/sql/item_vers.cc
+++ b/sql/item_vers.cc
@@ -142,7 +142,9 @@ Item_func_trt_id::val_int()
   else
   {
     MYSQL_TIME commit_ts;
-    if (args[0]->get_date(current_thd, &commit_ts, date_mode_t(0)))
+    THD *thd= current_thd;
+    Datetime::Options opt(TIME_CONV_NONE, thd);
+    if (args[0]->get_date(thd, &commit_ts, opt))
     {
       null_value= true;
       return 0;
diff --git a/sql/my_decimal.cc b/sql/my_decimal.cc
index de7018c53cb..6927b181d92 100644
--- a/sql/my_decimal.cc
+++ b/sql/my_decimal.cc
@@ -268,7 +268,8 @@ int str2my_decimal(uint mask, const char *from, size_t length,
   integer part cannot be larger that 1e18 (otherwise it's an overflow).
   fractional part is microseconds.
 */
-bool my_decimal2seconds(const my_decimal *d, ulonglong *sec, ulong *microsec)
+bool my_decimal2seconds(const my_decimal *d, ulonglong *sec,
+                        ulong *microsec, ulong *nanosec)
 {
   int pos;
   
@@ -286,6 +287,7 @@ bool my_decimal2seconds(const my_decimal *d, ulonglong *sec, ulong *microsec)
   }
 
   *microsec= d->frac ? static_cast<longlong>(d->buf[pos+1]) / (DIG_BASE/1000000) : 0;
+  *nanosec=  d->frac ? static_cast<longlong>(d->buf[pos+1]) % (DIG_BASE/1000000) : 0;
 
   if (pos > 1)
   {
diff --git a/sql/my_decimal.h b/sql/my_decimal.h
index b22c686cc90..c196d43e001 100644
--- a/sql/my_decimal.h
+++ b/sql/my_decimal.h
@@ -362,7 +362,8 @@ inline bool str_set_decimal(const my_decimal *val, String *str,
 }
 
 
-bool my_decimal2seconds(const my_decimal *d, ulonglong *sec, ulong *microsec);
+bool my_decimal2seconds(const my_decimal *d, ulonglong *sec,
+                        ulong *microsec, ulong *nanosec);
 
 my_decimal *seconds2my_decimal(bool sign, ulonglong sec, ulong microsec,
                                my_decimal *d);
diff --git a/sql/sp.cc b/sql/sp.cc
index 723f30ec85d..d37ea543219 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -200,7 +200,8 @@ TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] =
     "'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
     "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
     "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH',"
-    "'EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')") },
+    "'EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT',"
+    "'TIME_ROUND_FRACTIONAL')") },
     { NULL, 0 }
   },
   {
diff --git a/sql/sql_basic_types.h b/sql/sql_basic_types.h
index 5062817f911..a790b68fc0c 100644
--- a/sql/sql_basic_types.h
+++ b/sql/sql_basic_types.h
@@ -26,13 +26,15 @@ typedef int64 query_id_t;
 
 /*
   "fuzzydate" with strict data type control.
+  Represents a mixture of *only* data type conversion flags, without rounding.
   Please keep "explicit" in constructors and conversion methods.
 */
-class date_mode_t
+class date_conv_mode_t
 {
 public:
   enum value_t
   {
+    CONV_NONE=          0U,
     /*
       FUZZY_DATES is used for the result will only be used for comparison
       purposes. Conversion is as relaxed as possible.
@@ -41,17 +43,33 @@ public:
     TIME_ONLY=          4U,
     INTERVAL_hhmmssff=  8U,
     INTERVAL_DAY=      16U,
+    RANGE0_LAST=       INTERVAL_DAY,
     NO_ZERO_IN_DATE=   (1UL << 23),  // MODE_NO_ZERO_IN_DATE
     NO_ZERO_DATE=      (1UL << 24),  // MODE_NO_ZERO_DATE
     INVALID_DATES=     (1UL << 25)   // MODE_INVALID_DATES
   };
 
+  /*
+    BIT-OR for all known values. Let's have a separate enum for it.
+    - We don't put this value "value_t", to avoid handling it in switch().
+    - We don't put this value as a static const inside the class,
+      because "gdb" would display it every time when we do "print"
+      for a time_round_mode_t value.
+    - We can't put into into a function returning this value, because
+      it's not allowed to use functions in static_assert.
+  */
+  enum known_values_t
+  {
+    KNOWN_MODES= FUZZY_DATES |
+                       TIME_ONLY | INTERVAL_hhmmssff | INTERVAL_DAY |
+                       NO_ZERO_IN_DATE | NO_ZERO_DATE | INVALID_DATES
+  };
 private:
   value_t m_mode;
 public:
 
   // Constructors
-  explicit date_mode_t(ulonglong fuzzydate)
+  explicit date_conv_mode_t(ulonglong fuzzydate)
    :m_mode((value_t) fuzzydate)
   { }
 
@@ -66,9 +84,148 @@ public:
   }
 
   // Unary operators
-  date_mode_t operator~() const
+  ulonglong operator~() const
   {
-    return date_mode_t(~m_mode);
+    return ~m_mode;
+  }
+
+  // Dyadic bitwise operators
+  date_conv_mode_t operator&(const date_conv_mode_t &other) const
+  {
+    return date_conv_mode_t(m_mode & other.m_mode);
+  }
+  date_conv_mode_t operator&(const ulonglong other) const
+  {
+    return date_conv_mode_t(m_mode & other);
+  }
+
+  date_conv_mode_t operator|(const date_conv_mode_t &other) const
+  {
+    return date_conv_mode_t(m_mode | other.m_mode);
+  }
+
+  // Dyadic bitwise assignment operators
+  date_conv_mode_t &operator&=(const date_conv_mode_t &other)
+  {
+    m_mode= value_t(m_mode & other.m_mode);
+    return *this;
+  }
+
+  date_conv_mode_t &operator|=(const date_conv_mode_t &other)
+  {
+    m_mode= value_t(m_mode | other.m_mode);
+    return *this;
+  }
+};
+
+
+/*
+  Fractional rounding mode for temporal data types.
+*/
+class time_round_mode_t
+{
+public:
+  enum value_t
+  {
+    /*
+      Use FRAC_NONE when the value needs no rounding nor truncation,
+      because it is already known not to haveany fractional digits outside
+      of the requested precision.
+    */
+    FRAC_NONE= 0,
+    FRAC_TRUNCATE= date_conv_mode_t::RANGE0_LAST << 1,  // 32
+    FRAC_ROUND=    date_conv_mode_t::RANGE0_LAST << 2   // 64
+  };
+  // BIT-OR for all known values. See comments in time_conv_mode_t.
+  enum known_values_t
+  {
+    KNOWN_MODES= FRAC_TRUNCATE | FRAC_ROUND
+  };
+private:
+  value_t m_mode;
+public:
+  // Constructors
+  explicit time_round_mode_t(ulonglong mode)
+   :m_mode((value_t) mode)
+  {
+    DBUG_ASSERT(mode == FRAC_NONE ||
+                mode == FRAC_TRUNCATE ||
+                mode == FRAC_ROUND);
+  }
+  // Conversion operators
+  explicit operator ulonglong() const
+  {
+    return m_mode;
+  }
+  value_t mode() const
+  {
+    return m_mode;
+  }
+  // Comparison operators
+  bool operator==(const time_round_mode_t &other)
+  {
+    return m_mode == other.m_mode;
+  }
+};
+
+
+/*
+  "fuzzydate" with strict data type control.
+  Used as a parameter to get_date() and represents a mixture of:
+  - data type conversion flags
+  - fractional second rounding flags
+  Please keep "explicit" in constructors and conversion methods.
+*/
+class date_mode_t
+{
+public:
+  enum value_t
+  {
+    CONV_NONE=         date_conv_mode_t::CONV_NONE,         // 0
+    FUZZY_DATES=       date_conv_mode_t::FUZZY_DATES,       // 1
+    TIME_ONLY=         date_conv_mode_t::TIME_ONLY,         // 4
+    INTERVAL_hhmmssff= date_conv_mode_t::INTERVAL_hhmmssff, // 8
+    INTERVAL_DAY=      date_conv_mode_t::INTERVAL_DAY,      // 16
+    FRAC_TRUNCATE=     time_round_mode_t::FRAC_TRUNCATE,    // 32
+    FRAC_ROUND=        time_round_mode_t::FRAC_ROUND,       // 64
+    NO_ZERO_IN_DATE=   date_conv_mode_t::NO_ZERO_IN_DATE,   // (1UL << 23)
+    NO_ZERO_DATE=      date_conv_mode_t::NO_ZERO_DATE,      // (1UL << 24)
+    INVALID_DATES=     date_conv_mode_t::INVALID_DATES,     // (1UL << 25)
+  };
+protected:
+  value_t m_mode;
+public:
+
+  // Constructors
+  explicit date_mode_t(ulonglong fuzzydate)
+   :m_mode((value_t) fuzzydate)
+  { }
+
+  // Conversion operators
+  explicit operator ulonglong() const
+  {
+    return m_mode;
+  }
+  explicit operator bool() const
+  {
+    return m_mode != 0;
+  }
+  explicit operator date_conv_mode_t() const
+  {
+    return date_conv_mode_t(ulonglong(m_mode) & date_conv_mode_t::KNOWN_MODES);
+  }
+  explicit operator time_round_mode_t() const
+  {
+    return time_round_mode_t(ulonglong(m_mode) & time_round_mode_t::KNOWN_MODES);
+  }
+  // Unary operators
+  ulonglong operator~() const
+  {
+    return ~m_mode;
+  }
+  bool operator!() const
+  {
+    return !m_mode;
   }
 
   // Dyadic bitwise operators
@@ -76,6 +233,10 @@ public:
   {
     return date_mode_t(m_mode & other.m_mode);
   }
+  date_mode_t operator&(ulonglong other) const
+  {
+    return date_mode_t(m_mode & other);
+  }
 
   date_mode_t operator|(const date_mode_t &other) const
   {
@@ -94,22 +255,81 @@ public:
     m_mode= value_t(m_mode | other.m_mode);
     return *this;
   }
+
+  date_mode_t &operator|=(const date_conv_mode_t &other)
+  {
+    m_mode= value_t(m_mode | ulonglong(other));
+    return *this;
+  }
 };
 
 
-const date_mode_t
-  TIME_FUZZY_DATES            (date_mode_t::value_t::FUZZY_DATES),
-  TIME_TIME_ONLY              (date_mode_t::value_t::TIME_ONLY),
-  TIME_INTERVAL_hhmmssff      (date_mode_t::value_t::INTERVAL_hhmmssff),
-  TIME_INTERVAL_DAY           (date_mode_t::value_t::INTERVAL_DAY),
-  TIME_NO_ZERO_IN_DATE        (date_mode_t::value_t::NO_ZERO_IN_DATE),
-  TIME_NO_ZERO_DATE           (date_mode_t::value_t::NO_ZERO_DATE),
-  TIME_INVALID_DATES          (date_mode_t::value_t::INVALID_DATES);
+// Bitwise OR out-of-class operators for data type mixtures
+static inline date_mode_t operator|(const date_mode_t &a,
+                                    const date_conv_mode_t &b)
+{
+  return date_mode_t(ulonglong(a) | ulonglong(b));
+}
+
+static inline date_mode_t operator|(const date_conv_mode_t &a,
+                                    const time_round_mode_t &b)
+{
+  return date_mode_t(ulonglong(a) | ulonglong(b));
+}
+
+
+static inline date_mode_t operator|(const date_conv_mode_t &a,
+                                    const date_mode_t &b)
+{
+  return date_mode_t(ulonglong(a) | ulonglong(b));
+}
+
+
+// Bitwise AND out-of-class operators for data type mixtures
+static inline date_conv_mode_t operator&(const date_mode_t &a,
+                                         const date_conv_mode_t &b)
+{
+  return date_conv_mode_t(ulonglong(a) & ulonglong(b));
+}
+
+static inline date_conv_mode_t operator&(const date_conv_mode_t &a,
+                                         const date_mode_t &b)
+{
+  return date_conv_mode_t(ulonglong(a) & ulonglong(b));
+}
+
+static inline date_conv_mode_t operator&(sql_mode_t &a,
+                                         const date_conv_mode_t &b)
+{
+  return date_conv_mode_t(a & ulonglong(b));
+}
+
+
+static const date_conv_mode_t
+  TIME_CONV_NONE              (date_conv_mode_t::CONV_NONE),
+  TIME_FUZZY_DATES            (date_conv_mode_t::FUZZY_DATES),
+  TIME_TIME_ONLY              (date_conv_mode_t::TIME_ONLY),
+  TIME_INTERVAL_hhmmssff      (date_conv_mode_t::INTERVAL_hhmmssff),
+  TIME_INTERVAL_DAY           (date_conv_mode_t::INTERVAL_DAY),
+  TIME_NO_ZERO_IN_DATE        (date_conv_mode_t::NO_ZERO_IN_DATE),
+  TIME_NO_ZERO_DATE           (date_conv_mode_t::NO_ZERO_DATE),
+  TIME_INVALID_DATES          (date_conv_mode_t::INVALID_DATES);
+
+// An often used combination
+static const date_conv_mode_t
+  TIME_NO_ZEROS               (date_conv_mode_t::NO_ZERO_DATE|
+                               date_conv_mode_t::NO_ZERO_IN_DATE);
 
 // Flags understood by str_to_xxx, number_to_xxx, check_date
-static const date_mode_t
+static const date_conv_mode_t
   TIME_MODE_FOR_XXX_TO_DATE   (date_mode_t::NO_ZERO_IN_DATE |
                                date_mode_t::NO_ZERO_DATE    |
                                date_mode_t::INVALID_DATES);
 
+static const time_round_mode_t
+  TIME_FRAC_NONE              (time_round_mode_t::FRAC_NONE),
+  TIME_FRAC_TRUNCATE          (time_round_mode_t::FRAC_TRUNCATE),
+  TIME_FRAC_ROUND             (time_round_mode_t::FRAC_ROUND);
+
+
 #endif
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 8174a8b313b..55766af383d 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -158,6 +158,7 @@ enum enum_binlog_row_image {
 #define MODE_PAD_CHAR_TO_FULL_LENGTH    (1ULL << 31)
 #define MODE_EMPTY_STRING_IS_NULL       (1ULL << 32)
 #define MODE_SIMULTANEOUS_ASSIGNMENT    (1ULL << 33)
+#define MODE_TIME_ROUND_FRACTIONAL      (1ULL << 34)
 
 /* Bits for different old style modes */
 #define OLD_MODE_NO_DUP_KEY_WARNINGS_WITH_IGNORE	(1 << 0)
@@ -3419,6 +3420,11 @@ public:
   {
     return Timeval(query_start(), query_start_sec_part());
   }
+  time_round_mode_t temporal_round_mode() const
+  {
+    return variables.sql_mode & MODE_TIME_ROUND_FRACTIONAL ?
+           TIME_FRAC_ROUND : TIME_FRAC_TRUNCATE;
+  }
 
 private:
   struct {
@@ -4957,13 +4963,17 @@ my_eof(THD *thd)
   (A)->variables.sql_log_bin_off= 0;}
 
 
-inline date_mode_t sql_mode_for_dates(THD *thd)
+inline date_conv_mode_t sql_mode_for_dates(THD *thd)
 {
+  static_assert((date_conv_mode_t::KNOWN_MODES &
+                time_round_mode_t::KNOWN_MODES) == 0,
+                "date_conv_mode_t and time_round_mode_t must use different "
+                "bit values");
   static_assert(MODE_NO_ZERO_DATE    == date_mode_t::NO_ZERO_DATE &&
                 MODE_NO_ZERO_IN_DATE == date_mode_t::NO_ZERO_IN_DATE &&
                 MODE_INVALID_DATES   == date_mode_t::INVALID_DATES,
                 "sql_mode_t and date_mode_t values must be equal");
-  return date_mode_t(thd->variables.sql_mode &
+  return date_conv_mode_t(thd->variables.sql_mode &
           (MODE_NO_ZERO_DATE | MODE_NO_ZERO_IN_DATE | MODE_INVALID_DATES));
 }
 
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 96e07b19dd8..6b530a95efb 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -8217,6 +8217,7 @@ static int get_part_iter_for_interval_via_mapping(partition_info *part_info,
              field->type() == MYSQL_TYPE_DATETIME))
         {
           /* Monotonic, but return NULL for dates with zeros in month/day. */
+          DBUG_ASSERT(field->cmp_type() == TIME_RESULT); // No rounding/truncation
           zero_in_start_date= field->get_date(&start_date, date_mode_t(0));
           DBUG_PRINT("info", ("zero start %u %04d-%02d-%02d",
                               zero_in_start_date, start_date.year,
@@ -8241,6 +8242,7 @@ static int get_part_iter_for_interval_via_mapping(partition_info *part_info,
         !part_info->part_expr->null_value)
     {
       MYSQL_TIME end_date;
+      DBUG_ASSERT(field->cmp_type() == TIME_RESULT); // No rounding/truncation
       bool zero_in_end_date= field->get_date(&end_date, date_mode_t(0));
       /*
         This is an optimization for TO_DAYS()/TO_SECONDS() to avoid scanning
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 35a4e1e47c3..3977df675b3 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -289,8 +289,8 @@ ulong convert_month_to_period(ulong month)
 
 
 bool
-check_date_with_warn(THD *thd, const MYSQL_TIME *ltime, date_mode_t fuzzydate,
-                     timestamp_type ts_type)
+check_date_with_warn(THD *thd, const MYSQL_TIME *ltime,
+                     date_conv_mode_t fuzzydate, timestamp_type ts_type)
 {
   int unused;
   if (check_date(ltime, fuzzydate, &unused))
@@ -372,34 +372,37 @@ public:
 
 
 /* Character set-aware version of ascii_to_datetime_or_date_or_time() */
-bool Temporal::str_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *st,
+bool Temporal::str_to_datetime_or_date_or_time(THD *thd, MYSQL_TIME_STATUS *st,
                                                const char *str, size_t length,
                                                CHARSET_INFO *cs,
                                                date_mode_t fuzzydate)
 {
   TemporalAsciiBuffer tmp(str, length, cs);
-  return ascii_to_datetime_or_date_or_time(st, tmp.str, tmp.length, fuzzydate);
+  return ascii_to_datetime_or_date_or_time(st, tmp.str, tmp.length, fuzzydate)||
+         add_nanoseconds(thd, &st->warnings, fuzzydate, st->nanoseconds);
 }
 
 
 /* Character set-aware version of str_to_datetime_or_date() */
-bool Temporal::str_to_datetime_or_date(MYSQL_TIME_STATUS *status,
+bool Temporal::str_to_datetime_or_date(THD *thd, MYSQL_TIME_STATUS *status,
                                        const char *str, size_t length,
                                        CHARSET_INFO *cs,
                                        date_mode_t flags)
 {
   TemporalAsciiBuffer tmp(str, length, cs);
-  return ascii_to_datetime_or_date(status, tmp.str, tmp.length, flags);
+  return ascii_to_datetime_or_date(status, tmp.str, tmp.length, flags) ||
+         add_nanoseconds(thd, &status->warnings, flags, status->nanoseconds);
 }
 
 
 /* Character set-aware version of ascii_to_temporal() */
-bool Temporal::str_to_temporal(MYSQL_TIME_STATUS *status,
+bool Temporal::str_to_temporal(THD *thd, MYSQL_TIME_STATUS *status,
                                const char *str, size_t length, CHARSET_INFO *cs,
                                date_mode_t flags)
 {
   TemporalAsciiBuffer tmp(str, length, cs);
-  return ascii_to_temporal(status, tmp.str, tmp.length, flags);
+  return ascii_to_temporal(status, tmp.str, tmp.length, flags) ||
+         add_nanoseconds(thd, &status->warnings, flags, status->nanoseconds);
 }
 
 
@@ -1307,7 +1310,7 @@ time_to_datetime(THD *thd, const MYSQL_TIME *from, MYSQL_TIME *to)
 bool
 time_to_datetime_with_warn(THD *thd,
                            const MYSQL_TIME *from, MYSQL_TIME *to,
-                           date_mode_t fuzzydate)
+                           date_conv_mode_t fuzzydate)
 {
   int warn= 0;
   DBUG_ASSERT(from->time_type == MYSQL_TIMESTAMP_TIME);
diff --git a/sql/sql_time.h b/sql/sql_time.h
index 433374e2e9a..cfbff205667 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -57,7 +57,7 @@ bool int_to_datetime_with_warn(THD *thd, const Longlong_hybrid &nr,
 bool time_to_datetime(THD *thd, const MYSQL_TIME *tm, MYSQL_TIME *dt);
 bool time_to_datetime_with_warn(THD *thd,
                                 const MYSQL_TIME *tm, MYSQL_TIME *dt,
-                                date_mode_t fuzzydate);
+                                date_conv_mode_t fuzzydate);
 
 inline void datetime_to_date(MYSQL_TIME *ltime)
 {
@@ -166,13 +166,20 @@ non_zero_date(const MYSQL_TIME *ltime)
           non_zero_hhmmssuu(ltime));
 }
 static inline bool
-check_date(const MYSQL_TIME *ltime, date_mode_t flags, int *was_cut)
+check_date(const MYSQL_TIME *ltime, date_conv_mode_t flags, int *was_cut)
 {
  return check_date(ltime, non_zero_date(ltime),
                    ulonglong(flags & TIME_MODE_FOR_XXX_TO_DATE), was_cut);
 }
-bool check_date_with_warn(THD *thd, const MYSQL_TIME *ltime, date_mode_t fuzzy_date,
-                          timestamp_type ts_type);
+bool check_date_with_warn(THD *thd, const MYSQL_TIME *ltime,
+                          date_conv_mode_t fuzzy_date, timestamp_type ts_type);
+static inline bool
+check_date_with_warn(THD *thd, const MYSQL_TIME *ltime,
+                          date_mode_t fuzzydate, timestamp_type ts_type)
+{
+  return check_date_with_warn(thd, ltime, date_conv_mode_t(fuzzydate), ts_type);
+}
+
 bool adjust_time_range_with_warn(THD *thd, MYSQL_TIME *ltime, uint dec);
 
 longlong pack_time(const MYSQL_TIME *my_time);
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 60c3747f2ea..cd993e0524e 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -156,12 +156,24 @@ VDec_op::VDec_op(Item_func_hybrid_field_type *item)
 }
 
 
-date_mode_t Temporal::sql_mode_for_dates(THD *thd)
+date_conv_mode_t Temporal::sql_mode_for_dates(THD *thd)
 {
   return ::sql_mode_for_dates(thd);
 }
 
 
+time_round_mode_t Temporal::default_round_mode(THD *thd)
+{
+  return thd->temporal_round_mode();
+}
+
+
+time_round_mode_t Timestamp::default_round_mode(THD *thd)
+{
+  return thd->temporal_round_mode();
+}
+
+
 my_decimal *Temporal::to_decimal(my_decimal *to) const
 {
   return date2my_decimal(this, to);
@@ -183,8 +195,8 @@ void Temporal::make_from_str(THD *thd, Warn *warn,
                   push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
                                ER_YES, ErrConvString(str, length,cs).ptr()););
 
-  if (str_to_temporal(warn, str, length, cs, fuzzydate))
-    make_fuzzy_date(&warn->warnings, fuzzydate);
+  if (str_to_temporal(thd, warn, str, length, cs, fuzzydate))
+    make_fuzzy_date(&warn->warnings, date_conv_mode_t(fuzzydate));
   if (warn->warnings)
     warn->set_str(str, length, &my_charset_bin);
 }
@@ -197,14 +209,14 @@ Temporal_hybrid::Temporal_hybrid(THD *thd, Item *item, date_mode_t fuzzydate)
 }
 
 
-void Sec6::make_from_decimal(const my_decimal *d)
+void Sec6::make_from_decimal(const my_decimal *d, ulong *nanoseconds)
 {
-  m_neg= my_decimal2seconds(d, &m_sec, &m_usec);
+  m_neg= my_decimal2seconds(d, &m_sec, &m_usec, nanoseconds);
   m_truncated= (m_sec >= LONGLONG_MAX);
 }
 
 
-void Sec6::make_from_double(double nr)
+void Sec6::make_from_double(double nr, ulong *nanoseconds)
 {
   if ((m_neg= nr < 0))
     nr= -nr;
@@ -216,7 +228,9 @@ void Sec6::make_from_double(double nr)
   else
   {
     m_sec= (ulonglong) nr;
-    m_usec= (ulong) ((nr - floor(nr)) * 1000000);
+    m_usec= (ulong) ((nr - floor(nr)) * 1000000000);
+    *nanoseconds= m_usec % 1000;
+    m_usec/= 1000;
   }
 }
 
@@ -235,8 +249,8 @@ bool Sec6::convert_to_mysql_time(THD *thd, int *warn, MYSQL_TIME *ltime,
   bool rc= fuzzydate & (TIME_INTERVAL_hhmmssff | TIME_INTERVAL_DAY) ?
              to_datetime_or_to_interval_hhmmssff(ltime, warn) :
            fuzzydate & TIME_TIME_ONLY ?
-             to_datetime_or_time(ltime, warn, fuzzydate) :
-             to_datetime_or_date(ltime, warn, fuzzydate);
+             to_datetime_or_time(ltime, warn, date_conv_mode_t(fuzzydate)) :
+             to_datetime_or_date(ltime, warn, date_conv_mode_t(fuzzydate));
   DBUG_ASSERT(*warn || !rc);
   if (truncated())
     *warn|= MYSQL_TIME_WARN_TRUNCATED;
@@ -259,7 +273,7 @@ void Temporal::push_conversion_warnings(THD *thd, bool totally_useless_value, in
 }
 
 
-VSec6::VSec6(THD *thd, Item *item, const char *type_str, ulonglong limit)
+VSec9::VSec9(THD *thd, Item *item, const char *type_str, ulonglong limit)
 {
   if (item->decimals == 0)
   { // optimize for an important special case
@@ -277,7 +291,7 @@ VSec6::VSec6(THD *thd, Item *item, const char *type_str, ulonglong limit)
   else if (item->cmp_type() == REAL_RESULT)
   {
     double nr= item->val_real();
-    make_from_double(nr);
+    make_from_double(nr, &m_nsec);
     m_is_null= item->null_value;
     if (!m_is_null && m_sec > limit)
     {
@@ -293,7 +307,7 @@ VSec6::VSec6(THD *thd, Item *item, const char *type_str, ulonglong limit)
   else
   {
     VDec tmp(item);
-    (m_is_null= tmp.is_null()) ? reset() : make_from_decimal(tmp.ptr());
+    (m_is_null= tmp.is_null()) ? reset() : make_from_decimal(tmp.ptr(), &m_nsec);
     if (!m_is_null && m_sec > limit)
     {
       m_sec= limit;
@@ -352,7 +366,8 @@ const LEX_CSTRING Interval_DDhhmmssff::m_type_name=
 
 Interval_DDhhmmssff::Interval_DDhhmmssff(THD *thd, Status *st,
                                          bool push_warnings,
-                                         Item *item, ulong max_hour)
+                                         Item *item, ulong max_hour,
+                                         time_round_mode_t mode, uint dec)
 {
   switch (item->cmp_type()) {
   case ROW_RESULT:
@@ -361,7 +376,8 @@ Interval_DDhhmmssff::Interval_DDhhmmssff(THD *thd, Status *st,
     break;
   case TIME_RESULT:
     {
-      if (item->get_date(thd, this, TIME_TIME_ONLY))
+      // Rounding mode is not important here
+      if (item->get_date(thd, this, Options(TIME_TIME_ONLY, TIME_FRAC_NONE)))
         time_type= MYSQL_TIMESTAMP_NONE;
       else if (time_type != MYSQL_TIMESTAMP_TIME)
       {
@@ -392,6 +408,8 @@ Interval_DDhhmmssff::Interval_DDhhmmssff(THD *thd, Status *st,
       }
       else
       {
+        if (mode == TIME_FRAC_ROUND)
+          time_round_or_set_max(dec, &st->warnings, max_hour, st->nanoseconds);
         if (hour > max_hour)
         {
           st->warnings|= MYSQL_TIME_WARN_OUT_OF_RANGE;
@@ -450,7 +468,8 @@ uint Interval_DDhhmmssff::fsp(THD *thd, Item *item)
   if (!item->const_item() || item->is_expensive())
     return TIME_SECOND_PART_DIGITS;
   Status st;
-  Interval_DDhhmmssff it(thd, &st, false/*no warnings*/, item, UINT_MAX32);
+  Interval_DDhhmmssff it(thd, &st, false/*no warnings*/, item, UINT_MAX32,
+                         TIME_FRAC_TRUNCATE, TIME_SECOND_PART_DIGITS);
   return it.is_valid_interval_DDhhmmssff() ? st.precision :
                                              TIME_SECOND_PART_DIGITS;
 }
@@ -459,13 +478,101 @@ uint Interval_DDhhmmssff::fsp(THD *thd, Item *item)
 void Time::make_from_item(THD *thd, int *warn, Item *item, const Options opt)
 {
   *warn= 0;
-  if (item->get_date(thd, this, opt.get_date_flags()))
+  if (item->get_date(thd, this, opt))
     time_type= MYSQL_TIMESTAMP_NONE;
   else
     valid_MYSQL_TIME_to_valid_value(thd, warn, opt);
 }
 
 
+static uint msec_round_add[7]=
+{
+  500000000,
+  50000000,
+  5000000,
+  500000,
+  50000,
+  5000,
+  0
+};
+
+
+Sec9 & Sec9::round(uint dec)
+{
+  DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
+  if (Sec6::add_nanoseconds(m_nsec + msec_round_add[dec]))
+    m_sec++;
+  m_nsec= 0;
+  Sec6::trunc(dec);
+  return *this;
+}
+
+
+void Timestamp::round_or_set_max(uint dec, int *warn)
+{
+  DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
+  if (add_nanoseconds_usec(msec_round_add[dec]) &&
+      tv_sec++ >= TIMESTAMP_MAX_VALUE)
+  {
+    tv_sec= TIMESTAMP_MAX_VALUE;
+    tv_usec= TIME_MAX_SECOND_PART;
+    *warn|= MYSQL_TIME_WARN_OUT_OF_RANGE;
+  }
+  my_timeval_trunc(this, dec);
+}
+
+
+bool Temporal::add_nanoseconds_with_round(THD *thd, int *warn,
+                                          date_conv_mode_t mode,
+                                          ulong nsec)
+{
+  switch (time_type) {
+  case MYSQL_TIMESTAMP_TIME:
+  {
+    ulong max_hour= (mode & (TIME_INTERVAL_DAY | TIME_INTERVAL_hhmmssff)) ?
+                    TIME_MAX_INTERVAL_HOUR : TIME_MAX_HOUR;
+    time_round_or_set_max(6, warn, max_hour, nsec);
+    return false;
+  }
+  case MYSQL_TIMESTAMP_DATETIME:
+    return datetime_round_or_invalidate(thd, 6, warn, nsec);
+  case MYSQL_TIMESTAMP_DATE:
+    return false;
+  case MYSQL_TIMESTAMP_NONE:
+    return false;
+  case MYSQL_TIMESTAMP_ERROR:
+    break;
+  }
+  DBUG_ASSERT(0);
+  return false;
+}
+
+
+void Temporal::time_round_or_set_max(uint dec, int *warn,
+                                     ulong max_hour, ulong nsec)
+{
+  DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
+  if (add_nanoseconds_mmssff(nsec) && ++hour > max_hour)
+  {
+    time_hhmmssff_set_max(max_hour);
+    *warn|= MYSQL_TIME_WARN_OUT_OF_RANGE;
+  }
+  my_time_trunc(this, dec);
+}
+
+
+void Time::round_or_set_max(uint dec, int *warn, ulong nsec)
+{
+  Temporal::time_round_or_set_max(dec, warn, TIME_MAX_HOUR, nsec);
+  DBUG_ASSERT(is_valid_time_slow());
+}
+
+
+void Time::round_or_set_max(uint dec, int *warn)
+{
+  round_or_set_max(dec, warn, msec_round_add[dec]);
+}
+
 /**
   Create from a DATETIME by subtracting a given number of days,
   implementing an optimized version of calc_time_diff().
@@ -586,9 +693,10 @@ Time::Time(int *warn, bool neg, ulonglong hour, uint minute, const Sec6 &second)
 }
 
 
-void Temporal_with_date::make_from_item(THD *thd, Item *item, date_mode_t flags)
+void Temporal_with_date::make_from_item(THD *thd, Item *item,
+                                        date_mode_t fuzzydate)
 {
-  flags&= ~TIME_TIME_ONLY;
+  date_conv_mode_t flags= date_conv_mode_t(fuzzydate) & ~TIME_TIME_ONLY;
   /*
     Some TIME type items return error when trying to do get_date()
     without TIME_TIME_ONLY set (e.g. Item_field for Field_time).
@@ -596,10 +704,11 @@ void Temporal_with_date::make_from_item(THD *thd, Item *item, date_mode_t flags)
     In the legacy time->datetime conversion mode we do not add TIME_TIME_ONLY
     and leave it to get_date() to check date.
   */
-  date_mode_t time_flag= (item->field_type() == MYSQL_TYPE_TIME &&
+  date_conv_mode_t time_flag= (item->field_type() == MYSQL_TYPE_TIME &&
               !(thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST)) ?
-              TIME_TIME_ONLY : date_mode_t(0);
-  if (item->get_date(thd, this, flags | time_flag))
+              TIME_TIME_ONLY : TIME_CONV_NONE;
+  Options opt(flags | time_flag, time_round_mode_t(fuzzydate));
+  if (item->get_date(thd, this, opt))
     time_type= MYSQL_TIMESTAMP_NONE;
   else if (time_type == MYSQL_TIMESTAMP_TIME)
   {
@@ -612,22 +721,17 @@ void Temporal_with_date::make_from_item(THD *thd, Item *item, date_mode_t flags)
 }
 
 
-void Temporal_with_date::make_from_item(THD *thd, Item *item)
+void Temporal_with_date::check_date_or_invalidate(int *warn,
+                                                  date_conv_mode_t flags)
 {
-  return make_from_item(thd, item, sql_mode_for_dates(thd));
-}
-
-
-void Temporal_with_date::check_date_or_invalidate(int *warn, date_mode_t flags)
-{
-  if (check_date(this, pack_time(this) != 0,
-                 ulonglong(flags & TIME_MODE_FOR_XXX_TO_DATE), warn))
+  if (::check_date(this, pack_time(this) != 0,
+                   ulonglong(flags & TIME_MODE_FOR_XXX_TO_DATE), warn))
     time_type= MYSQL_TIMESTAMP_NONE;
 }
 
 
 void Datetime::make_from_time(THD *thd, int *warn, const MYSQL_TIME *from,
-                              date_mode_t flags)
+                              date_conv_mode_t flags)
 {
   DBUG_ASSERT(from->time_type == MYSQL_TIMESTAMP_TIME);
   if (time_to_datetime(thd, from, this))
@@ -641,7 +745,7 @@ void Datetime::make_from_time(THD *thd, int *warn, const MYSQL_TIME *from,
 
 
 void Datetime::make_from_datetime(THD *thd, int *warn, const MYSQL_TIME *from,
-                                  date_mode_t flags)
+                                  date_conv_mode_t flags)
 {
   DBUG_ASSERT(from->time_type == MYSQL_TIMESTAMP_DATE ||
               from->time_type == MYSQL_TIMESTAMP_DATETIME);
@@ -667,7 +771,7 @@ Datetime::Datetime(THD *thd, const timeval &tv)
 
 
 Datetime::Datetime(THD *thd, int *warn, const MYSQL_TIME *from,
-                   date_mode_t flags)
+                   date_conv_mode_t flags)
 {
   DBUG_ASSERT(bool(flags & TIME_TIME_ONLY) == false);
   switch (from->time_type) {
@@ -687,6 +791,82 @@ Datetime::Datetime(THD *thd, int *warn, const MYSQL_TIME *from,
 }
 
 
+bool Temporal::datetime_add_nanoseconds_or_invalidate(THD *thd, int *warn, ulong nsec)
+{
+  if (!add_nanoseconds_mmssff(nsec))
+    return false;
+  /*
+    Overflow happened on minutes. Now we need to add 1 hour to the value.
+    Catch a special case for the maximum possible date and hour==23, to
+    truncate '9999-12-31 23:59:59.9999999' (with 7 fractional digits)
+          to '9999-12-31 23:59:59.999999'  (with 6 fractional digits),
+    with a warning, instead of returning an error, so this statement:
+      INSERT INTO (datetime_column) VALUES ('9999-12-31 23:59:59.9999999');
+    inserts a value truncated to 6 fractional digits, instead of zero
+    date '0000-00-00 00:00:00.000000'.
+  */
+  if (year == 9999 && month == 12 && day == 31 && hour == 23)
+  {
+    minute= 59;
+    second= 59;
+    second_part= 999999;
+    *warn= MYSQL_TIME_WARN_OUT_OF_RANGE;
+    return false;
+  }
+  INTERVAL interval;
+  memset(&interval, 0, sizeof(interval));
+  interval.hour= 1;
+  /* date_add_interval cannot handle bad dates */
+  if (check_date(TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE, warn) ||
+      date_add_interval(thd, this, INTERVAL_HOUR, interval))
+  {
+    make_from_out_of_range(warn);
+    return true;
+  }
+  return false;
+}
+
+
+bool Temporal::datetime_round_or_invalidate(THD *thd, uint dec, int *warn, ulong nsec)
+{
+  DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
+  if (datetime_add_nanoseconds_or_invalidate(thd, warn, nsec))
+    return true;
+  my_time_trunc(this, dec);
+  return false;
+
+}
+
+
+bool Datetime::round_or_invalidate(THD *thd, uint dec, int *warn)
+{
+  return round_or_invalidate(thd, dec, warn, msec_round_add[dec]);
+}
+
+
+Datetime_from_temporal::Datetime_from_temporal(THD *thd, Item *temporal,
+                                               date_conv_mode_t fuzzydate)
+ :Datetime(thd, temporal, Options(fuzzydate, TIME_FRAC_NONE))
+{
+  // Exact rounding mode does not matter
+  DBUG_ASSERT(temporal->cmp_type() == TIME_RESULT);
+}
+
+
+Datetime_truncation_not_needed::Datetime_truncation_not_needed(THD *thd, Item *item,
+                                                               date_conv_mode_t mode)
+ :Datetime(thd, item, Options(mode, TIME_FRAC_NONE))
+{
+  /*
+    The called Datetime() constructor only would truncate nanoseconds if they
+    existed (but we know there were no nanoseconds). Here we assert that there
+    are also no microsecond digits outside of the scale specified in "dec".
+  */
+  DBUG_ASSERT(!is_valid_datetime() || fraction_remainder(item->decimals) == 0);
+}
+
+/********************************************************************/
+
 uint Type_std_attributes::count_max_decimals(Item **item, uint nitems)
 {
   uint res= 0;
@@ -3151,14 +3331,16 @@ void Type_handler_row::Item_update_null_value(Item *item) const
 void Type_handler_time_common::Item_update_null_value(Item *item) const
 {
   MYSQL_TIME ltime;
-  (void) item->get_date(current_thd, &ltime, TIME_TIME_ONLY);
+  THD *thd= current_thd;
+  (void) item->get_date(thd, &ltime, Time::Options(TIME_TIME_ONLY, thd));
 }
 
 
 void Type_handler_temporal_with_date::Item_update_null_value(Item *item) const
 {
   MYSQL_TIME ltime;
-  (void) item->get_date(current_thd, &ltime, sql_mode_for_dates(current_thd));
+  THD *thd= current_thd;
+  (void) item->get_date(thd, &ltime, Datetime::Options(thd));
 }
 
 
@@ -5013,7 +5195,7 @@ bool Type_handler_temporal_result::
   */
   return func->get_date_native(thd, ltime,
                                fuzzydate & TIME_TIME_ONLY ?
-                               sql_mode_for_dates(thd) :
+                               Datetime::Options(thd) :
                                fuzzydate);
 }
 
@@ -5847,14 +6029,15 @@ uint Type_handler_string_result::Item_temporal_precision(THD *thd, Item *item,
   String *tmp;
   MYSQL_TIME_STATUS status;
   DBUG_ASSERT(item->is_fixed());
+  // Nanosecond rounding is not needed here, for performance purposes
   if ((tmp= item->val_str(&buf)) &&
       (is_time ?
        Time(thd, &status, tmp->ptr(), tmp->length(), tmp->charset(),
-            Time::Options(TIME_TIME_ONLY,
+            Time::Options(TIME_TIME_ONLY, TIME_FRAC_TRUNCATE,
                           Time::DATETIME_TO_TIME_YYYYMMDD_TRUNCATE)).
          is_valid_time() :
-       Datetime(&status, tmp->ptr(), tmp->length(), tmp->charset(),
-                TIME_FUZZY_DATES).
+       Datetime(thd, &status, tmp->ptr(), tmp->length(), tmp->charset(),
+                Datetime::Options(TIME_FUZZY_DATES, TIME_FRAC_TRUNCATE)).
          is_valid_datetime()))
     return MY_MIN(status.precision, TIME_SECOND_PART_DIGITS);
   return MY_MIN(item->decimals, TIME_SECOND_PART_DIGITS);
@@ -6104,7 +6287,8 @@ bool Type_handler_temporal_with_date::
        Item_save_in_value(THD *thd, Item *item, st_value *value) const
 {
   value->m_type= DYN_COL_DATETIME;
-  item->get_date(thd, &value->value.m_time, sql_mode_for_dates(thd));
+  item->get_date(thd, &value->value.m_time,
+                 Datetime::Options(thd, TIME_FRAC_NONE));
   return check_null(item, value);
 }
 
@@ -6298,7 +6482,7 @@ bool Type_handler::
        Item_send_datetime(Item *item, Protocol *protocol, st_value *buf) const
 {
   item->get_date(protocol->thd, &buf->value.m_time,
-                 sql_mode_for_dates(protocol->thd));
+                 Datetime::Options(protocol->thd));
   if (!item->null_value)
     return protocol->store(&buf->value.m_time, item->decimals);
   return protocol->store_null();
@@ -6309,7 +6493,7 @@ bool Type_handler::
        Item_send_date(Item *item, Protocol *protocol, st_value *buf) const
 {
   item->get_date(protocol->thd, &buf->value.m_time,
-                 sql_mode_for_dates(protocol->thd));
+                 Date::Options(protocol->thd));
   if (!item->null_value)
     return protocol->store_date(&buf->value.m_time);
   return protocol->store_null();
@@ -7502,8 +7686,8 @@ int Type_handler_temporal_with_date::stored_field_cmp_to_item(THD *thd,
                                                               Item *item) const
 {
   MYSQL_TIME field_time, item_time, item_time2, *item_time_cmp= &item_time;
-  field->get_date(&field_time, TIME_INVALID_DATES);
-  item->get_date(thd, &item_time, TIME_INVALID_DATES);
+  field->get_date(&field_time, Datetime::Options(TIME_INVALID_DATES, thd));
+  item->get_date(thd, &item_time, Datetime::Options(TIME_INVALID_DATES, thd));
   if (item_time.time_type == MYSQL_TIMESTAMP_TIME &&
       time_to_datetime(thd, &item_time, item_time_cmp= &item_time2))
     return 1;
@@ -7516,8 +7700,8 @@ int Type_handler_time_common::stored_field_cmp_to_item(THD *thd,
                                                        Item *item) const
 {
   MYSQL_TIME field_time, item_time;
-  field->get_time(&field_time);
-  item->get_time(thd, &item_time);
+  field->get_date(&field_time, Time::Options(thd));
+  item->get_date(thd, &item_time, Time::Options(thd));
   return my_time_compare(&field_time, &item_time);
 }
 
@@ -7612,7 +7796,7 @@ Type_handler_date_common::create_literal_item(THD *thd,
 {
   Temporal::Warn st;
   Item_literal *item= NULL;
-  Temporal_hybrid tmp(thd, &st, str, length, cs, sql_mode_for_dates(thd));
+  Temporal_hybrid tmp(thd, &st, str, length, cs, Temporal_hybrid::Options(thd));
   if (tmp.is_valid_temporal() &&
       tmp.get_mysql_time()->time_type == MYSQL_TIMESTAMP_DATE &&
       !have_important_literal_warnings(&st))
@@ -7632,7 +7816,7 @@ Type_handler_temporal_with_date::create_literal_item(THD *thd,
 {
   Temporal::Warn st;
   Item_literal *item= NULL;
-  Temporal_hybrid tmp(thd, &st, str, length, cs, sql_mode_for_dates(thd));
+  Temporal_hybrid tmp(thd, &st, str, length, cs, Temporal_hybrid::Options(thd));
   if (tmp.is_valid_temporal() &&
       tmp.get_mysql_time()->time_type == MYSQL_TIMESTAMP_DATETIME &&
       !have_important_literal_warnings(&st))
@@ -7653,7 +7837,7 @@ Type_handler_time_common::create_literal_item(THD *thd,
 {
   MYSQL_TIME_STATUS st;
   Item_literal *item= NULL;
-  Time::Options opt(TIME_TIME_ONLY, Time::DATETIME_TO_TIME_DISALLOW);
+  Time::Options opt(TIME_TIME_ONLY, thd, Time::DATETIME_TO_TIME_DISALLOW);
   Time tmp(thd, &st, str, length, cs, opt);
   if (tmp.is_valid_time() &&
       !have_important_literal_warnings(&st))
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 273542c201c..a5d8f4ae6df 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -216,8 +216,8 @@ protected:
   ulong     m_usec;      // The fractional part, between 0 and 999999
   bool      m_neg;       // false if positive, true of negative
   bool      m_truncated; // Indicates if the constructor truncated the value
-  void make_from_decimal(const my_decimal *d);
-  void make_from_double(double d);
+  void make_from_decimal(const my_decimal *d, ulong *nanoseconds);
+  void make_from_double(double d, ulong *nanoseconds);
   void make_from_int(const Longlong_hybrid &nr)
   {
     m_neg= nr.neg();
@@ -230,14 +230,27 @@ protected:
     m_sec= m_usec= m_neg= m_truncated= 0;
   }
   Sec6() { }
+  bool add_nanoseconds(uint nanoseconds)
+  {
+    DBUG_ASSERT(nanoseconds <= 1000000000);
+    if (nanoseconds < 500)
+      return false;
+    m_usec+= (nanoseconds + 500) / 1000;
+    if (m_usec < 1000000)
+      return false;
+    m_usec%= 1000000;
+    return true;
+  }
 public:
   explicit Sec6(double nr)
   {
-    make_from_double(nr);
+    ulong nanoseconds;
+    make_from_double(nr, &nanoseconds);
   }
   explicit Sec6(const my_decimal *d)
   {
-    make_from_decimal(d);
+    ulong nanoseconds;
+    make_from_decimal(d, &nanoseconds);
   }
   explicit Sec6(const Longlong_hybrid &nr)
   {
@@ -303,7 +316,8 @@ protected:
   }
 public:
   // [-][DD]hhhmmss.ff,  YYMMDDhhmmss.ff, YYYYMMDDhhmmss.ff
-  bool to_datetime_or_time(MYSQL_TIME *to, int *warn, date_mode_t mode) const
+  bool to_datetime_or_time(MYSQL_TIME *to, int *warn,
+                           date_conv_mode_t mode) const
   {
     bool rc= m_sec > 9999999 && m_sec <= 99991231235959ULL && !m_neg ?
              ::number_to_datetime_or_date(m_sec, m_usec, to,
@@ -316,7 +330,8 @@ public:
     Convert a number in formats YYYYMMDDhhmmss.ff or YYMMDDhhmmss.ff to
     TIMESTAMP'YYYY-MM-DD hh:mm:ss.ff'
   */
-  bool to_datetime_or_date(MYSQL_TIME *to, int *warn, date_mode_t flags) const
+  bool to_datetime_or_date(MYSQL_TIME *to, int *warn,
+                           date_conv_mode_t flags) const
   {
     if (m_neg)
     {
@@ -349,6 +364,11 @@ public:
     ltime->second_part= m_usec;
     return false;
   }
+  Sec6 &trunc(uint dec)
+  {
+    m_usec-= my_time_fraction_remainder(m_usec, dec);
+    return *this;
+  }
   size_t to_string(char *to, size_t nbytes) const
   {
     return m_usec ?
@@ -360,11 +380,45 @@ public:
 };
 
 
-class VSec6: public Sec6
+class Sec9: public Sec6
+{
+protected:
+  ulong m_nsec; // Nanoseconds 0..999
+  void make_from_int(const Longlong_hybrid &nr)
+  {
+    Sec6::make_from_int(nr);
+    m_nsec= 0;
+  }
+  Sec9() { }
+public:
+  Sec9(const my_decimal *d)
+  {
+    Sec6::make_from_decimal(d, &m_nsec);
+  }
+  Sec9(double d)
+  {
+    Sec6::make_from_double(d, &m_nsec);
+  }
+  ulong nsec() const { return m_nsec; }
+  Sec9 &trunc(uint dec)
+  {
+    m_nsec= 0;
+    Sec6::trunc(dec);
+    return *this;
+  }
+  Sec9 &round(uint dec);
+  Sec9 &round(uint dec, time_round_mode_t mode)
+  {
+    return mode == TIME_FRAC_TRUNCATE  ? trunc(dec) : round(dec);
+  }
+};
+
+
+class VSec9: public Sec9
 {
   bool m_is_null;
 public:
-  VSec6(THD *thd, Item *item, const char *type_str, ulonglong limit);
+  VSec9(THD *thd, Item *item, const char *type_str, ulonglong limit);
   bool is_null() const { return m_is_null; }
 };
 
@@ -524,7 +578,24 @@ public:
   };
 
 public:
-  static date_mode_t sql_mode_for_dates(THD *thd);
+  static date_conv_mode_t sql_mode_for_dates(THD *thd);
+  static time_round_mode_t default_round_mode(THD *thd);
+  class Options: public date_mode_t
+  {
+  public:
+    explicit Options(date_mode_t flags)
+     :date_mode_t(flags)
+    { }
+    Options(date_conv_mode_t flags, time_round_mode_t round_mode)
+     :date_mode_t(flags | round_mode)
+    {
+      DBUG_ASSERT(ulonglong(flags) <= UINT_MAX32);
+    }
+    Options(date_conv_mode_t flags, THD *thd)
+     :Options(flags, default_round_mode(thd))
+    { }
+  };
+
   bool is_valid_temporal() const
   {
     DBUG_ASSERT(time_type != MYSQL_TIMESTAMP_ERROR);
@@ -550,7 +621,7 @@ public:
     TIME/DATE/DATETIME failed. We return a zero date if allowed,
     otherwise - null.
   */
-  void make_fuzzy_date(int *warn, date_mode_t fuzzydate)
+  void make_fuzzy_date(int *warn, date_conv_mode_t fuzzydate)
   {
     /*
       In the following scenario:
@@ -590,14 +661,21 @@ protected:
                       const Sec6 &nr, date_mode_t mode)
   {
     if (nr.convert_to_mysql_time(thd, &st->warnings, this, mode))
-      make_fuzzy_date(&st->warnings, mode);
+      make_fuzzy_date(&st->warnings, date_conv_mode_t(mode));
+  }
+  void make_from_sec9(THD *thd, MYSQL_TIME_STATUS *st,
+                      const Sec9 &nr, date_mode_t mode)
+  {
+    if (nr.convert_to_mysql_time(thd, &st->warnings, this, mode) ||
+        add_nanoseconds(thd, &st->warnings, mode, nr.nsec()))
+      make_fuzzy_date(&st->warnings, date_conv_mode_t(mode));
   }
   void make_from_str(THD *thd, Warn *warn,
                      const char *str, size_t length, CHARSET_INFO *cs,
                      date_mode_t fuzzydate);
   void make_from_double(THD *thd, Warn *warn, double nr, date_mode_t mode)
   {
-    make_from_sec6(thd, warn, Sec6(nr), mode);
+    make_from_sec9(thd, warn, Sec9(nr), mode);
     if (warn->warnings)
       warn->set_double(nr);
   }
@@ -615,7 +693,7 @@ protected:
   void make_from_decimal(THD *thd, Warn *warn,
                          const my_decimal *nr, date_mode_t mode)
   {
-    make_from_sec6(thd, warn, Sec6(nr), mode);
+    make_from_sec9(thd, warn, Sec9(nr), mode);
     if (warn->warnings)
       warn->set_decimal(nr);
   }
@@ -670,15 +748,15 @@ protected:
     return rc;
   }
   // Character set aware versions for string conversion routines
-  bool str_to_temporal(MYSQL_TIME_STATUS *st,
+  bool str_to_temporal(THD *thd, MYSQL_TIME_STATUS *st,
                        const char *str, size_t length,
                        CHARSET_INFO *cs, date_mode_t fuzzydate);
-  bool str_to_datetime_or_date_or_time(MYSQL_TIME_STATUS *st,
+  bool str_to_datetime_or_date_or_time(THD *thd, MYSQL_TIME_STATUS *st,
                                        const char *str, size_t length,
-                                       CHARSET_INFO *cs, date_mode_t fuzzydate);
-  bool str_to_datetime_or_date(MYSQL_TIME_STATUS *st,
+                                       CHARSET_INFO *cs, date_mode_t mode);
+  bool str_to_datetime_or_date(THD *thd, MYSQL_TIME_STATUS *st,
                                const char *str, size_t length,
-                               CHARSET_INFO *cs, date_mode_t fuzzydate);
+                               CHARSET_INFO *cs, date_mode_t mode);
 
   bool has_valid_mmssff() const
   {
@@ -694,6 +772,67 @@ protected:
   {
     return year == 0 && month == 0 && day == 0;
   }
+  bool check_date(date_conv_mode_t flags, int *warn) const
+  {
+    return ::check_date(this, flags, warn);
+  }
+  void time_hhmmssff_set_max(ulong max_hour)
+  {
+    hour= max_hour;
+    minute= TIME_MAX_MINUTE;
+    second= TIME_MAX_SECOND;
+    second_part= TIME_MAX_SECOND_PART;
+  }
+  /*
+    Add nanoseconds to ssff
+    retval   true if seconds overflowed (the caller should increment minutes)
+             false if no overflow happened
+  */
+  bool add_nanoseconds_ssff(uint nanoseconds)
+  {
+    DBUG_ASSERT(nanoseconds <= 1000000000);
+    if (nanoseconds < 500)
+      return false;
+    second_part+= (nanoseconds + 500) / 1000;
+    if (second_part < 1000000)
+      return false;
+    second_part%= 1000000;
+    if (second < 59)
+    {
+      second++;
+      return false;
+    }
+    second= 0;
+    return true;
+  }
+  /*
+    Add nanoseconds to mmssff
+    retval   true if hours overflowed (the caller should increment hours)
+             false if no overflow happened
+  */
+  bool add_nanoseconds_mmssff(uint nanoseconds)
+  {
+    if (!add_nanoseconds_ssff(nanoseconds))
+      return false;
+    if (minute < 59)
+    {
+      minute++;
+      return false;
+    }
+    minute= 0;
+    return true;
+  }
+  void time_round_or_set_max(uint dec, int *warn, ulong max_hour, ulong nsec);
+  bool datetime_add_nanoseconds_or_invalidate(THD *thd, int *warn, ulong nsec);
+  bool datetime_round_or_invalidate(THD *thd, uint dec, int *warn, ulong nsec);
+  bool add_nanoseconds_with_round(THD *thd, int *warn,
+                                  date_conv_mode_t mode, ulong nsec);
+  bool add_nanoseconds(THD *thd, int *warn, date_mode_t mode, ulong nsec)
+  {
+    date_conv_mode_t cmode= date_conv_mode_t(mode);
+    return time_round_mode_t(mode) == TIME_FRAC_ROUND ?
+           add_nanoseconds_with_round(thd, warn, cmode, nsec) : false;
+  }
 public:
   static void *operator new(size_t size, MYSQL_TIME *ltime) throw()
   {
@@ -717,10 +856,28 @@ public:
 class Temporal_hybrid: public Temporal
 {
 public:
+  class Options: public Temporal::Options
+  {
+  public:
+    Options(THD *thd)
+     :Temporal::Options(sql_mode_for_dates(thd), default_round_mode(thd))
+    { }
+    Options(date_conv_mode_t flags, time_round_mode_t round_mode)
+     :Temporal::Options(flags, round_mode)
+    { }
+    explicit Options(const Temporal::Options &opt)
+     :Temporal::Options(opt)
+    { }
+    explicit Options(date_mode_t fuzzydate)
+     :Temporal::Options(fuzzydate)
+    { }
+  };
+
+public:
   // Contructors for Item
   Temporal_hybrid(THD *thd, Item *item, date_mode_t fuzzydate);
   Temporal_hybrid(THD *thd, Item *item)
-   :Temporal_hybrid(thd, item, sql_mode_for_dates(thd))
+   :Temporal_hybrid(thd, item, Options(thd))
   { }
   Temporal_hybrid(Item *item)
    :Temporal_hybrid(current_thd, item)
@@ -964,14 +1121,20 @@ public:
     DBUG_ASSERT(fsp <= TIME_SECOND_PART_DIGITS);
     return max_int_part_char_length() + (fsp ? 1 : 0) + fsp;
   }
+
 public:
   Interval_DDhhmmssff(THD *thd, Status *st, bool push_warnings,
-                      Item *item, ulong max_hour);
-  Interval_DDhhmmssff(THD *thd, Item *item)
+                      Item *item, ulong max_hour,
+                      time_round_mode_t mode, uint dec);
+  Interval_DDhhmmssff(THD *thd, Item *item, uint dec)
   {
     Status st;
-    new(this) Interval_DDhhmmssff(thd, &st, true, item, max_useful_hour());
+    new(this) Interval_DDhhmmssff(thd, &st, true, item, max_useful_hour(),
+                                  default_round_mode(thd), dec);
   }
+  Interval_DDhhmmssff(THD *thd, Item *item)
+   :Interval_DDhhmmssff(thd, item, TIME_SECOND_PART_DIGITS)
+  { }
   const MYSQL_TIME *get_mysql_time() const
   {
     DBUG_ASSERT(is_valid_interval_DDhhmmssff_slow());
@@ -1028,27 +1191,35 @@ public:
     DATETIME_TO_TIME_YYYYMMDD_00000000_ONLY,
     DATETIME_TO_TIME_MINUS_CURRENT_DATE
   };
-  class Options
+  class Options: public Temporal::Options
   {
-    date_mode_t             m_get_date_flags;
     datetime_to_time_mode_t m_datetime_to_time_mode;
   public:
-    Options()
-     :m_get_date_flags(flags_for_get_date()),
-      m_datetime_to_time_mode(DATETIME_TO_TIME_YYYYMMDD_000000DD_MIX_TO_HOURS)
+    Options(THD *thd)
+     :Temporal::Options(default_flags_for_get_date(), default_round_mode(thd)),
+      m_datetime_to_time_mode(default_datetime_to_time_mode())
     { }
-    Options(date_mode_t flags)
-     :m_get_date_flags(flags),
-      m_datetime_to_time_mode(DATETIME_TO_TIME_YYYYMMDD_000000DD_MIX_TO_HOURS)
+    Options(date_conv_mode_t flags, THD *thd)
+     :Temporal::Options(flags, default_round_mode(thd)),
+      m_datetime_to_time_mode(default_datetime_to_time_mode())
     { }
-    Options(date_mode_t flags, datetime_to_time_mode_t dtmode)
-     :m_get_date_flags(flags),
+    Options(date_conv_mode_t flags, THD *thd, datetime_to_time_mode_t dtmode)
+     :Temporal::Options(flags, default_round_mode(thd)),
       m_datetime_to_time_mode(dtmode)
     { }
-    date_mode_t get_date_flags() const
-    { return m_get_date_flags; }
+    Options(date_conv_mode_t fuzzydate, time_round_mode_t round_mode,
+            datetime_to_time_mode_t datetime_to_time_mode)
+     :Temporal::Options(fuzzydate, round_mode),
+       m_datetime_to_time_mode(datetime_to_time_mode)
+    { }
+
     datetime_to_time_mode_t datetime_to_time_mode() const
     { return m_datetime_to_time_mode; }
+
+    static datetime_to_time_mode_t default_datetime_to_time_mode()
+    {
+      return DATETIME_TO_TIME_YYYYMMDD_000000DD_MIX_TO_HOURS;
+    }
   };
   /*
     CAST(AS TIME) historically does not mix days to hours.
@@ -1058,14 +1229,28 @@ public:
   class Options_for_cast: public Options
   {
   public:
-    Options_for_cast()
-     :Options(flags_for_get_date(), DATETIME_TO_TIME_YYYYMMDD_TRUNCATE)
+    Options_for_cast(THD *thd)
+     :Options(default_flags_for_get_date(), default_round_mode(thd),
+              DATETIME_TO_TIME_YYYYMMDD_TRUNCATE)
     { }
-    Options_for_cast(date_mode_t mode)
-     :Options(flags_for_get_date() | (mode & TIME_FUZZY_DATES),
+    Options_for_cast(date_mode_t mode, THD *thd)
+     :Options(default_flags_for_get_date() | (mode & TIME_FUZZY_DATES),
+              default_round_mode(thd),
               DATETIME_TO_TIME_YYYYMMDD_TRUNCATE)
     { }
   };
+
+  class Options_cmp: public Options
+  {
+  public:
+    Options_cmp(THD *thd)
+     :Options(comparison_flags_for_get_date(), thd)
+    { }
+    Options_cmp(THD *thd, datetime_to_time_mode_t dtmode)
+     :Options(comparison_flags_for_get_date(),
+              default_round_mode(thd), dtmode)
+    { }
+  };
 private:
   bool is_valid_value_slow() const
   {
@@ -1199,6 +1384,15 @@ private:
       time_type= MYSQL_TIMESTAMP_NONE;
     DBUG_ASSERT(is_valid_value_slow());
   }
+public:
+  void round_or_set_max(uint dec, int *warn, ulong nsec);
+private:
+  void round_or_set_max(uint dec, int *warn);
+
+  /*
+    All make_from_xxx() methods initialize *warn.
+    The old value gets lost.
+  */
   void make_from_datetime_move_day_to_hour(int *warn, const MYSQL_TIME *from);
   void make_from_datetime_with_days_diff(int *warn, const MYSQL_TIME *from,
                                          long curdays);
@@ -1213,7 +1407,7 @@ public:
   Time(int *warn, bool neg, ulonglong hour, uint minute, const Sec6 &second);
   Time() { time_type= MYSQL_TIMESTAMP_NONE; }
   Time(Item *item)
-   :Time(current_thd, item, Options())
+   :Time(current_thd, item)
   { }
   Time(THD *thd, Item *item, const Options opt)
   {
@@ -1221,18 +1415,18 @@ public:
     make_from_item(thd, &warn, item, opt);
   }
   Time(THD *thd, Item *item)
-   :Time(thd, item, Options())
+   :Time(thd, item, Options(thd))
   { }
   Time(int *warn, const MYSQL_TIME *from, long curdays);
   Time(THD *thd, MYSQL_TIME_STATUS *status,
        const char *str, size_t len, CHARSET_INFO *cs,
        const Options opt)
   {
-    if (str_to_datetime_or_date_or_time(status, str, len, cs,
-                                        opt.get_date_flags()))
+    if (str_to_datetime_or_date_or_time(thd, status, str, len, cs, opt))
       time_type= MYSQL_TIMESTAMP_NONE;
     // The below call will optionally add notes to already collected warnings:
-    xxx_to_time_result_to_valid_value(thd, &status->warnings, opt);
+    else
+      xxx_to_time_result_to_valid_value(thd, &status->warnings, opt);
   }
 
 protected:
@@ -1242,34 +1436,50 @@ protected:
       time_type= MYSQL_TIMESTAMP_NONE;
     xxx_to_time_result_to_valid_value(thd, warn, opt);
   }
+  Time(THD *thd, int *warn, const Sec9 &nr, const Options &opt)
+   :Time(thd, warn, static_cast<Sec6>(nr), opt)
+  {
+    if (is_valid_time() && time_round_mode_t(opt) == TIME_FRAC_ROUND)
+      round_or_set_max(6, warn, nr.nsec());
+  }
 
 public:
   Time(THD *thd, int *warn, const Longlong_hybrid &nr, const Options &opt)
    :Time(thd, warn, Sec6(nr), opt)
   { }
   Time(THD *thd, int *warn, double nr, const Options &opt)
-   :Time(thd, warn, Sec6(nr), opt)
+   :Time(thd, warn, Sec9(nr), opt)
   { }
   Time(THD *thd, int *warn, const my_decimal *d, const Options &opt)
-   :Time(thd, warn, Sec6(d), opt)
+   :Time(thd, warn, Sec9(d), opt)
   { }
 
   Time(THD *thd, Item *item, const Options opt, uint dec)
    :Time(thd, item, opt)
   {
-    trunc(dec);
+    round(dec, time_round_mode_t(opt));
   }
-  Time(int *warn, const MYSQL_TIME *from, long curdays, uint dec)
+  Time(int *warn, const MYSQL_TIME *from, long curdays,
+       const Time::Options &opt, uint dec)
    :Time(warn, from, curdays)
   {
-    trunc(dec);
+    round(dec, time_round_mode_t(opt), warn);
+  }
+  Time(int *warn, bool neg, ulonglong hour, uint minute, const Sec9 &second,
+       time_round_mode_t mode, uint dec)
+   :Time(warn, neg, hour, minute, second)
+  {
+    DBUG_ASSERT(is_valid_time());
+    if ((ulonglong) mode == (ulonglong) TIME_FRAC_ROUND)
+      round_or_set_max(6, warn, second.nsec());
+    round(dec, mode, warn);
   }
   Time(THD *thd, MYSQL_TIME_STATUS *status,
        const char *str, size_t len, CHARSET_INFO *cs,
        const Options &opt, uint dec)
    :Time(thd, status, str, len, cs, opt)
   {
-    trunc(dec);
+    round(dec, time_round_mode_t(opt), &status->warnings);
   }
   Time(THD *thd, int *warn, const Longlong_hybrid &nr,
        const Options &opt, uint dec)
@@ -1278,23 +1488,24 @@ public:
     /*
       Decimal digit truncation is needed here in case if nr was out
       of the supported TIME range, so "this" was set to '838:59:59.999999'.
+      We always do truncation (not rounding) here, independently from "opt".
     */
     trunc(dec);
   }
   Time(THD *thd, int *warn, double nr, const Options &opt, uint dec)
    :Time(thd, warn, nr, opt)
   {
-    trunc(dec);
+    round(dec, time_round_mode_t(opt), warn);
   }
   Time(THD *thd, int *warn, const my_decimal *d, const Options &opt, uint dec)
    :Time(thd, warn, d, opt)
   {
-    trunc(dec);
+    round(dec, time_round_mode_t(opt), warn);
   }
 
-  static date_mode_t flags_for_get_date()
+  static date_conv_mode_t default_flags_for_get_date()
   { return TIME_TIME_ONLY | TIME_INVALID_DATES; }
-  static date_mode_t comparison_flags_for_get_date()
+  static date_conv_mode_t comparison_flags_for_get_date()
   { return TIME_TIME_ONLY | TIME_INVALID_DATES | TIME_FUZZY_DATES; }
   bool is_valid_time() const
   {
@@ -1367,6 +1578,12 @@ public:
   {
     return is_valid_time() ? Temporal::to_packed() : 0;
   }
+  long fraction_remainder(uint dec) const
+  {
+    DBUG_ASSERT(is_valid_time());
+    return Temporal::fraction_remainder(dec);
+  }
+
   Time &trunc(uint dec)
   {
     if (is_valid_time())
@@ -1374,6 +1591,32 @@ public:
     DBUG_ASSERT(is_valid_value_slow());
     return *this;
   }
+  Time &round(uint dec, int *warn)
+  {
+    if (is_valid_time())
+      round_or_set_max(dec, warn);
+    DBUG_ASSERT(is_valid_value_slow());
+    return *this;
+  }
+  Time &round(uint dec, time_round_mode_t mode, int *warn)
+  {
+    switch (mode.mode()) {
+    case time_round_mode_t::FRAC_NONE:
+      DBUG_ASSERT(fraction_remainder(dec) == 0);
+      return trunc(dec);
+    case time_round_mode_t::FRAC_TRUNCATE:
+      return trunc(dec);
+    case time_round_mode_t::FRAC_ROUND:
+      return round(dec, warn);
+    }
+    return *this;
+  }
+  Time &round(uint dec, time_round_mode_t mode)
+  {
+    int warn= 0;
+    return round(dec, mode, &warn);
+  }
+
 };
 
 
@@ -1402,10 +1645,23 @@ public:
 
 class Temporal_with_date: public Temporal
 {
+public:
+  class Options: public Temporal::Options
+  {
+  public:
+    Options(date_conv_mode_t fuzzydate, time_round_mode_t mode):
+     Temporal::Options(fuzzydate, mode)
+    {}
+    explicit Options(const Temporal::Options &opt)
+     :Temporal::Options(opt)
+    { }
+    explicit Options(date_mode_t mode)
+     :Temporal::Options(mode)
+    { }
+  };
 protected:
-  void check_date_or_invalidate(int *warn, date_mode_t flags);
+  void check_date_or_invalidate(int *warn, date_conv_mode_t flags);
   void make_from_item(THD *thd, Item *item, date_mode_t flags);
-  void make_from_item(THD *thd, Item *item);
 
   ulong daynr() const
   {
@@ -1430,39 +1686,35 @@ protected:
     uint week= calc_week(this, week_behaviour, &year);
     return week + year * 100;
   }
-
+public:
   Temporal_with_date()
   {
     time_type= MYSQL_TIMESTAMP_NONE;
   }
-  Temporal_with_date(THD *thd, Item *item, date_mode_t flags)
-  {
-    make_from_item(thd, item, flags);
-  }
-  Temporal_with_date(THD *thd, Item *item)
+  Temporal_with_date(THD *thd, Item *item, date_mode_t fuzzydate)
   {
-    make_from_item(thd, item);
+    make_from_item(thd, item, fuzzydate);
   }
   Temporal_with_date(int *warn, const Sec6 &nr, date_mode_t flags)
   {
     DBUG_ASSERT(bool(flags & TIME_TIME_ONLY) == false);
-    if (nr.to_datetime_or_date(this, warn, flags))
+    if (nr.to_datetime_or_date(this, warn, date_conv_mode_t(flags)))
       time_type= MYSQL_TIMESTAMP_NONE;
   }
-  Temporal_with_date(MYSQL_TIME_STATUS *status,
+  Temporal_with_date(THD *thd, MYSQL_TIME_STATUS *status,
                      const char *str, size_t len, CHARSET_INFO *cs,
                      date_mode_t flags)
   {
     DBUG_ASSERT(bool(flags & TIME_TIME_ONLY) == false);
-    if (str_to_datetime_or_date(status, str, len, cs, flags))
+    if (str_to_datetime_or_date(thd, status, str, len, cs, flags))
       time_type= MYSQL_TIMESTAMP_NONE;
   }
 public:
-  bool check_date_with_warn(THD *thd, date_mode_t flags)
+  bool check_date_with_warn(THD *thd, date_conv_mode_t flags)
   {
     return ::check_date_with_warn(thd, this, flags, MYSQL_TIMESTAMP_ERROR);
   }
-  static date_mode_t comparison_flags_for_get_date()
+  static date_conv_mode_t comparison_flags_for_get_date()
   { return TIME_INVALID_DATES | TIME_FUZZY_DATES; }
 };
 
@@ -1488,22 +1740,41 @@ class Date: public Temporal_with_date
     return !check_datetime_range(this);
   }
 public:
-  Date(THD *thd, Item *item, date_mode_t flags)
-   :Temporal_with_date(thd, item, flags)
+  class Options: public Temporal_with_date::Options
   {
-    if (time_type == MYSQL_TIMESTAMP_DATETIME)
-      datetime_to_date(this);
-    DBUG_ASSERT(is_valid_value_slow());
-  }
-  Date(THD *thd, Item *item)
-   :Temporal_with_date(thd, item)
+  public:
+    explicit Options(date_conv_mode_t fuzzydate)
+     :Temporal_with_date::Options(fuzzydate, TIME_FRAC_TRUNCATE)
+    { }
+    Options(THD *thd, time_round_mode_t mode)
+     :Temporal_with_date::Options(sql_mode_for_dates(thd), mode)
+    { }
+    explicit Options(THD *thd)
+     :Temporal_with_date::Options(sql_mode_for_dates(thd), TIME_FRAC_TRUNCATE)
+    { }
+    explicit Options(date_mode_t fuzzydate)
+     :Temporal_with_date::Options(fuzzydate)
+    { }
+  };
+public:
+  Date(Item *item, date_mode_t fuzzydate)
+   :Date(current_thd, item, fuzzydate)
+  { }
+  Date(THD *thd, Item *item, date_mode_t fuzzydate)
+   :Temporal_with_date(thd, item, fuzzydate)
   {
     if (time_type == MYSQL_TIMESTAMP_DATETIME)
       datetime_to_date(this);
     DBUG_ASSERT(is_valid_value_slow());
   }
+  Date(THD *thd, Item *item, date_conv_mode_t fuzzydate)
+   :Date(thd, item, Options(fuzzydate))
+  { }
+  Date(THD *thd, Item *item)
+   :Temporal_with_date(Date(thd, item, Options(thd, TIME_FRAC_TRUNCATE)))
+  { }
   Date(Item *item)
-   :Date(current_thd, item)
+   :Temporal_with_date(Date(current_thd, item))
   { }
   Date(const Temporal_with_date *d)
    :Temporal_with_date(*d)
@@ -1603,98 +1874,141 @@ class Datetime: public Temporal_with_date
     DBUG_ASSERT(time_type == MYSQL_TIMESTAMP_DATETIME);
     return !check_datetime_range(this);
   }
+  bool add_nanoseconds_or_invalidate(THD *thd, int *warn, ulong nsec)
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    bool rc= Temporal::datetime_add_nanoseconds_or_invalidate(thd, warn, nsec);
+    DBUG_ASSERT(is_valid_value_slow());
+    return rc;
+  }
   void date_to_datetime_if_needed()
   {
     if (time_type == MYSQL_TIMESTAMP_DATE)
       date_to_datetime(this);
   }
   void make_from_time(THD *thd, int *warn, const MYSQL_TIME *from,
-                      date_mode_t flags);
+                      date_conv_mode_t flags);
   void make_from_datetime(THD *thd, int *warn, const MYSQL_TIME *from,
-                          date_mode_t flags);
-public:
-  Datetime(THD *thd, Item *item, date_mode_t flags)
-   :Temporal_with_date(thd, item, flags)
+                          date_conv_mode_t flags);
+  bool round_or_invalidate(THD *thd, uint dec, int *warn);
+  bool round_or_invalidate(THD *thd, uint dec, int *warn, ulong nsec)
   {
-    date_to_datetime_if_needed();
+    DBUG_ASSERT(is_valid_datetime_slow());
+    bool rc= Temporal::datetime_round_or_invalidate(thd, dec, warn, nsec);
     DBUG_ASSERT(is_valid_value_slow());
+    return rc;
   }
-  Datetime(THD *thd, Item *item)
-   :Temporal_with_date(thd, item)
+public:
+
+  class Options: public Temporal_with_date::Options
+  {
+  public:
+    Options(date_conv_mode_t fuzzydate, time_round_mode_t nanosecond_rounding)
+     :Temporal_with_date::Options(fuzzydate, nanosecond_rounding)
+    { }
+    Options(THD *thd)
+     :Temporal_with_date::Options(sql_mode_for_dates(thd), default_round_mode(thd))
+    { }
+    Options(THD *thd, time_round_mode_t rounding_mode)
+     :Temporal_with_date::Options(sql_mode_for_dates(thd), rounding_mode)
+    { }
+    Options(date_conv_mode_t fuzzydate, THD *thd)
+     :Temporal_with_date::Options(fuzzydate, default_round_mode(thd))
+    { }
+  };
+
+  class Options_cmp: public Options
+  {
+  public:
+    Options_cmp(THD *thd)
+     :Options(comparison_flags_for_get_date(), thd)
+    { }
+  };
+
+public:
+  Datetime(THD *thd, Item *item, date_mode_t fuzzydate)
+   :Temporal_with_date(thd, item, fuzzydate)
   {
     date_to_datetime_if_needed();
     DBUG_ASSERT(is_valid_value_slow());
   }
+  Datetime(THD *thd, Item *item)
+   :Temporal_with_date(Datetime(thd, item, Options(thd)))
+  { }
   Datetime(Item *item)
    :Datetime(current_thd, item)
   { }
-  Datetime(THD *thd, int *warn, const MYSQL_TIME *from, date_mode_t flags);
+
+  Datetime(THD *thd, int *warn, const MYSQL_TIME *from, date_conv_mode_t flags);
   Datetime()
   {
     set_zero_time(this, MYSQL_TIMESTAMP_DATETIME);
   }
-  Datetime(MYSQL_TIME_STATUS *status,
+  Datetime(THD *thd, MYSQL_TIME_STATUS *status,
            const char *str, size_t len, CHARSET_INFO *cs,
-           date_mode_t flags)
-   :Temporal_with_date(status, str, len, cs, flags)
+           const date_mode_t fuzzydate)
+   :Temporal_with_date(thd, status, str, len, cs, fuzzydate)
   {
     date_to_datetime_if_needed();
     DBUG_ASSERT(is_valid_value_slow());
   }
 
 protected:
-  Datetime(int *warn, const Sec6 &nr, date_mode_t flags)
+  Datetime(THD *thd, int *warn, const Sec6 &nr, date_mode_t flags)
    :Temporal_with_date(warn, nr, flags)
   {
     date_to_datetime_if_needed();
     DBUG_ASSERT(is_valid_value_slow());
   }
+  Datetime(THD *thd, int *warn, const Sec9 &nr, date_mode_t fuzzydate)
+   :Datetime(thd, warn, static_cast<const Sec6>(nr), fuzzydate)
+  {
+    if (is_valid_datetime() &&
+        time_round_mode_t(fuzzydate) == TIME_FRAC_ROUND)
+      round_or_invalidate(thd, 6, warn, nr.nsec());
+    DBUG_ASSERT(is_valid_value_slow());
+  }
 
 public:
-  Datetime(int *warn, const Longlong_hybrid &nr, date_mode_t mode)
-   :Datetime(warn, Sec6(nr), mode)
+  Datetime(THD *thd, int *warn, const Longlong_hybrid &nr, date_mode_t mode)
+   :Datetime(thd, warn, Sec6(nr), mode)
   { }
-  Datetime(int *warn, double nr, date_mode_t fuzzydate)
-   :Datetime(warn, Sec6(nr), fuzzydate)
+  Datetime(THD *thd, int *warn, double nr, date_mode_t fuzzydate)
+   :Datetime(thd, warn, Sec9(nr), fuzzydate)
   { }
-  Datetime(int *warn, const my_decimal *d, date_mode_t fuzzydate)
-   :Datetime(warn, Sec6(d), fuzzydate)
+  Datetime(THD *thd, int *warn, const my_decimal *d, date_mode_t fuzzydate)
+   :Datetime(thd, warn, Sec9(d), fuzzydate)
   { }
   Datetime(THD *thd, const timeval &tv);
 
-  Datetime(THD *thd, Item *item, date_mode_t flags, uint dec)
-   :Datetime(thd, item, flags)
+  Datetime(THD *thd, Item *item, date_mode_t fuzzydate, uint dec)
+   :Datetime(thd, item, fuzzydate)
   {
-    trunc(dec);
+    int warn= 0;
+    round(thd, dec, time_round_mode_t(fuzzydate), &warn);
   }
-  Datetime(MYSQL_TIME_STATUS *status,
+  Datetime(THD *thd, MYSQL_TIME_STATUS *status,
            const char *str, size_t len, CHARSET_INFO *cs,
            date_mode_t fuzzydate, uint dec)
-   :Datetime(status, str, len, cs, fuzzydate)
+   :Datetime(thd, status, str, len, cs, fuzzydate)
   {
-    trunc(dec);
+    round(thd, dec, time_round_mode_t(fuzzydate), &status->warnings);
   }
-  Datetime(int *warn, double nr, date_mode_t fuzzydate, uint dec)
-   :Datetime(warn, nr, fuzzydate)
+  Datetime(THD *thd, int *warn, double nr, date_mode_t fuzzydate, uint dec)
+   :Datetime(thd, warn, nr, fuzzydate)
   {
-    trunc(dec);
+    round(thd, dec, time_round_mode_t(fuzzydate), warn);
   }
-  Datetime(int *warn, const my_decimal *d, date_mode_t fuzzydate, uint dec)
-   :Datetime(warn, d, fuzzydate)
+  Datetime(THD *thd, int *warn, const my_decimal *d, date_mode_t fuzzydate, uint dec)
+   :Datetime(thd, warn, d, fuzzydate)
   {
-    trunc(dec);
+    round(thd, dec, time_round_mode_t(fuzzydate), warn);
   }
   Datetime(THD *thd, int *warn, const MYSQL_TIME *from,
            date_mode_t fuzzydate, uint dec)
-   :Datetime(thd, warn, from, fuzzydate)
-  {
-    trunc(dec);
-  }
-  Datetime(THD *thd, const timeval &tv, uint dec)
-   :Datetime(thd, tv)
+   :Datetime(thd, warn, from, date_conv_mode_t(fuzzydate) & ~TIME_TIME_ONLY)
   {
-    DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
-    trunc(dec);
+    round(thd, dec, time_round_mode_t(fuzzydate), warn);
   }
 
   bool is_valid_datetime() const
@@ -1706,14 +2020,14 @@ public:
     DBUG_ASSERT(is_valid_value_slow());
     return time_type == MYSQL_TIMESTAMP_DATETIME;
   }
-  bool check_date(date_mode_t flags, int *warnings) const
+  bool check_date(date_conv_mode_t flags, int *warnings) const
   {
     DBUG_ASSERT(is_valid_datetime_slow());
     return ::check_date(this, (year || month || day),
                         ulonglong(flags & TIME_MODE_FOR_XXX_TO_DATE),
                         warnings);
   }
-  bool check_date(date_mode_t flags) const
+  bool check_date(date_conv_mode_t flags) const
   {
     int dummy; /* unused */
     return check_date(flags, &dummy);
@@ -1728,6 +2042,27 @@ public:
     DBUG_ASSERT(is_valid_datetime_slow());
     return Temporal_with_date::daynr();
   }
+  ulong dayofyear() const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return Temporal_with_date::dayofyear();
+  }
+  uint quarter() const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return Temporal_with_date::quarter();
+  }
+  uint week(uint week_behaviour) const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return Temporal_with_date::week(week_behaviour);
+  }
+  uint yearweek(uint week_behaviour) const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return Temporal_with_date::yearweek(week_behaviour);
+  }
+
   longlong hhmmss_to_seconds_abs() const
   {
     DBUG_ASSERT(is_valid_datetime_slow());
@@ -1800,6 +2135,12 @@ public:
   {
     return is_valid_datetime() ? Temporal::to_packed() : 0;
   }
+  long fraction_remainder(uint dec) const
+  {
+    DBUG_ASSERT(is_valid_datetime());
+    return Temporal::fraction_remainder(dec);
+  }
+
   Datetime &trunc(uint dec)
   {
     if (is_valid_datetime())
@@ -1807,21 +2148,129 @@ public:
     DBUG_ASSERT(is_valid_value_slow());
     return *this;
   }
+  Datetime &round(THD *thd, uint dec, int *warn)
+  {
+    if (is_valid_datetime())
+      round_or_invalidate(thd, dec, warn);
+    DBUG_ASSERT(is_valid_value_slow());
+    return *this;
+  }
+  Datetime &round(THD *thd, uint dec, time_round_mode_t mode, int *warn)
+  {
+    switch (mode.mode()) {
+    case time_round_mode_t::FRAC_NONE:
+      DBUG_ASSERT(fraction_remainder(dec) == 0);
+      return trunc(dec);
+    case time_round_mode_t::FRAC_TRUNCATE:
+      return trunc(dec);
+    case time_round_mode_t::FRAC_ROUND:
+      return round(thd, dec, warn);
+    }
+    return *this;
+  }
+  Datetime &round(THD *thd, uint dec, time_round_mode_t mode)
+  {
+    int warn= 0;
+    return round(thd, dec, mode, &warn);
+  }
+
+};
+
+
+/*
+  Datetime to be created from an Item who is known to be of a temporal
+  data type. For temporal data types we don't need nanosecond rounding
+  or truncation, as their precision is limited.
+*/
+class Datetime_from_temporal: public Datetime
+{
+public:
+  // The constructor DBUG_ASSERTs on a proper Item data type.
+  Datetime_from_temporal(THD *thd, Item *temporal, date_conv_mode_t flags);
+};
+
+
+/*
+  Datetime to be created from an Item who is known not to have digits outside
+  of the specified scale. So it's not important which rounding method to use.
+  TRUNCATE should work.
+  Typically, Item is of a temporal data type, but this is not strictly required.
+*/
+class Datetime_truncation_not_needed: public Datetime
+{
+public:
+  Datetime_truncation_not_needed(THD *thd, Item *item, date_conv_mode_t mode);
+  Datetime_truncation_not_needed(THD *thd, Item *item, date_mode_t mode)
+   :Datetime_truncation_not_needed(thd, item, date_conv_mode_t(mode))
+  { }
 };
 
 
 class Timestamp: protected Timeval
 {
+protected:
+  void round_or_set_max(uint dec, int *warn);
+  bool add_nanoseconds_usec(uint nanoseconds)
+  {
+    DBUG_ASSERT(nanoseconds <= 1000000000);
+    if (nanoseconds < 500)
+      return false;
+    tv_usec+= (nanoseconds + 500) / 1000;
+    if (tv_usec < 1000000)
+      return false;
+    tv_usec%= 1000000;
+    return true;
+  }
+public:
+  static date_conv_mode_t sql_mode_for_timestamp(THD *thd);
+  static time_round_mode_t default_round_mode(THD *thd);
+  class DatetimeOptions: public date_mode_t
+  {
+  public:
+    DatetimeOptions(date_conv_mode_t fuzzydate, time_round_mode_t round_mode)
+     :date_mode_t(fuzzydate | round_mode)
+    { }
+    DatetimeOptions(THD *thd)
+     :DatetimeOptions(sql_mode_for_timestamp(thd), default_round_mode(thd))
+    { }
+  };
 public:
   Timestamp(my_time_t timestamp, ulong sec_part)
    :Timeval(timestamp, sec_part)
   { }
   const struct timeval &tv() const { return *this; }
+  long fraction_remainder(uint dec) const
+  {
+    return my_time_fraction_remainder(tv_usec, dec);
+  }
   Timestamp &trunc(uint dec)
   {
     my_timeval_trunc(this, dec);
     return *this;
   }
+  Timestamp &round(uint dec, int *warn)
+  {
+    round_or_set_max(dec, warn);
+    return *this;
+  }
+  Timestamp &round(uint dec, time_round_mode_t mode, int *warn)
+  {
+    switch (mode.mode()) {
+    case time_round_mode_t::FRAC_NONE:
+      DBUG_ASSERT(fraction_remainder(dec) == 0);
+      return trunc(dec);
+    case time_round_mode_t::FRAC_TRUNCATE:
+      return trunc(dec);
+    case time_round_mode_t::FRAC_ROUND:
+      return round(dec, warn);
+    }
+    return *this;
+  }
+  Timestamp &round(uint dec, time_round_mode_t mode)
+  {
+    int warn= 0;
+    return round(dec, mode, &warn);
+  }
 };
 
 
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 92c7d329bb9..3a4871875e5 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3353,6 +3353,7 @@ static const char *sql_mode_names[]=
   "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", "TRADITIONAL",
   "NO_AUTO_CREATE_USER", "HIGH_NOT_PRECEDENCE", "NO_ENGINE_SUBSTITUTION",
   "PAD_CHAR_TO_FULL_LENGTH", "EMPTY_STRING_IS_NULL", "SIMULTANEOUS_ASSIGNMENT",
+  "TIME_ROUND_FRACTIONAL",
   0
 };
 
diff --git a/sql/sys_vars.ic b/sql/sys_vars.ic
index dbc3565e202..1c41cc22411 100644
--- a/sql/sys_vars.ic
+++ b/sql/sys_vars.ic
@@ -2659,7 +2659,8 @@ public:
     if (!Sys_var_enum::do_check(thd, var))
       return false;
     MYSQL_TIME ltime;
-    bool res= var->value->get_date(thd, &ltime, date_mode_t(0));
+    Datetime::Options opt(TIME_CONV_NONE, thd);
+    bool res= var->value->get_date(thd, &ltime, opt);
     if (!res)
     {
       var->save_result.ulonglong_value= SYSTEM_TIME_AS_OF;
@@ -2676,7 +2677,9 @@ private:
     {
       if (var->value)
       {
-        res= var->value->get_date(current_thd, &out.ltime, date_mode_t(0));
+        THD *thd= current_thd;
+        Datetime::Options opt(TIME_CONV_NONE, thd);
+        res= var->value->get_date(thd, &out.ltime, opt);
       }
       else // set DEFAULT from global var
       {