Repository: elixir-ecto/ecto
Pull request #2291 (2017-11-02 02:30:46)

add support for usec types

ebert[bot]: Ebert has finished reviewing this Pull Request and has found:

* 7 possible new issues (including those that may have been commented here).

You can see more details about this review at https://ebertapp.io/github/elixir-ecto/ecto/pulls/2291.

<!-- Ebert auto generated comment -->

josevalim: Thanks @dallincrane, this looks great. I would just like to ask you to revert the splitting you did on Ecto.Type and keep a single module. They share a lot of common logic and it would make the PR easier to review too. We can always move it apart later.
josevalim: This looks perfect now @dallincrane! :heart: There is still more work to do in other areas, would you like to do it or would you prefer if i merged it and finished it?

Here is what comes to mind:

  * We need docs in Ecto.Schema
  * Change test/ecto/repo/autogenerate_test.exs and test that generation works with both naive_datetime_usec and utc_datetime_usec (I would say they don't right now)
  * We need to add some integration tests, probably in integration_test/cases/interval.exs that uses usec types and make sure that everything works. See `test "datetime_add with utc_datetime"` at the end of the file for an example. Probably something exactly like that, except it uses the other types.
dallincrane: @josevalim I would love to keep working on this if that's alright!? I should be able to get some good work in on it this weekend
josevalim: > @josevalim I would love to keep working on this if that's alright!? I should be able to get some good work in on it this weekend

Excellent!
dallincrane: A quick list so its obvious where I'm at as I add commits

- [x] usec types for migrations
- [x] usec types for schemas
- [ ] usec types for timestamps autogen
- [ ] Ecto.Schema docs
- [ ] integration tests
dallincrane: @josevalim This should be ready for another review. Let me know if I'm missing anything. I'm more than happy to keep contributing!

Patch:
diff --git a/lib/ecto/adapters/mysql.ex b/lib/ecto/adapters/mysql.ex
index 8811618a..982d8d78 100644
--- a/lib/ecto/adapters/mysql.ex
+++ b/lib/ecto/adapters/mysql.ex
@@ -96,11 +96,10 @@ defmodule Ecto.Adapters.MySQL do
   differences and consult the documentation for your MySQL
   version.
 
-  Assuming your version of MySQL supports microsecond precision,
-  you will need to explicitly set it on the relevant columns
-  in your migration. For explicitly declared columns you can
-  add the `size: 6` option. If you're using the `timestamps()`
-  helper you can use `timestamps(size: 6)`.
+  If your version of MySQL supports microsecond precision, you
+  will be able to utilize Ecto's usec types. The precision for a
+  usec type will default to 6 but can be explicitly declared by
+  using the `precision` option.
   """
 
   # Inherit all behaviour from Ecto.Adapters.SQL
diff --git a/lib/ecto/adapters/mysql/connection.ex b/lib/ecto/adapters/mysql/connection.ex
index e71d6c97..f9635433 100644
--- a/lib/ecto/adapters/mysql/connection.ex
+++ b/lib/ecto/adapters/mysql/connection.ex
@@ -390,7 +390,7 @@ if Code.ensure_loaded?(Mariaex) do
 
     defp expr({:datetime_add, _, [datetime, count, interval]}, sources, query) do
       ["CAST(date_add(", expr(datetime, sources, query), ", ",
-       interval(count, interval, sources, query) | ") AS datetime)"]
+       interval(count, interval, sources, query) | ") AS datetime(6))"]
     end
 
     defp expr({:date_add, _, [date, count, interval]}, sources, query) do
@@ -668,6 +668,16 @@ if Code.ensure_loaded?(Mariaex) do
     defp options_expr(options),
       do: [?\s, to_string(options)]
 
+    defp column_type(type, _opts) when type in ~w(time utc_datetime naive_datetime)a,
+      do: ecto_to_db(type)
+
+    defp column_type(type, opts) when type in ~w(time_usec utc_datetime_usec naive_datetime_usec)a do
+      precision = Keyword.get(opts, :precision, 6)
+      type_name = ecto_to_db(type)
+
+      [type_name, ?(, to_string(precision), ?)]
+    end
+
     defp column_type(type, opts) do
       size      = Keyword.get(opts, :size)
       precision = Keyword.get(opts, :precision)
@@ -769,20 +779,23 @@ if Code.ensure_loaded?(Mariaex) do
     defp ecto_cast_to_db(type, query), do: ecto_to_db(type, query)
 
     defp ecto_to_db(type, query \\ nil)
-    defp ecto_to_db({:array, _}, query),      do: error!(query, "Array type is not supported by MySQL")
-    defp ecto_to_db(:id, _query),             do: "integer"
-    defp ecto_to_db(:serial, _query),         do: "bigint unsigned not null auto_increment"
-    defp ecto_to_db(:bigserial, _query),      do: "bigint unsigned not null auto_increment"
-    defp ecto_to_db(:binary_id, _query),      do: "binary(16)"
-    defp ecto_to_db(:string, _query),         do: "varchar"
-    defp ecto_to_db(:float, _query),          do: "double"
-    defp ecto_to_db(:binary, _query),         do: "blob"
-    defp ecto_to_db(:uuid, _query),           do: "binary(16)" # MySQL does not support uuid
-    defp ecto_to_db(:map, _query),            do: "text"
-    defp ecto_to_db({:map, _}, _query),       do: "text"
-    defp ecto_to_db(:utc_datetime, _query),   do: "datetime"
-    defp ecto_to_db(:naive_datetime, _query), do: "datetime"
-    defp ecto_to_db(other, _query),           do: Atom.to_string(other)
+    defp ecto_to_db({:array, _}, query),           do: error!(query, "Array type is not supported by MySQL")
+    defp ecto_to_db(:id, _query),                  do: "integer"
+    defp ecto_to_db(:serial, _query),              do: "bigint unsigned not null auto_increment"
+    defp ecto_to_db(:bigserial, _query),           do: "bigint unsigned not null auto_increment"
+    defp ecto_to_db(:binary_id, _query),           do: "binary(16)"
+    defp ecto_to_db(:string, _query),              do: "varchar"
+    defp ecto_to_db(:float, _query),               do: "double"
+    defp ecto_to_db(:binary, _query),              do: "blob"
+    defp ecto_to_db(:uuid, _query),                do: "binary(16)" # MySQL does not support uuid
+    defp ecto_to_db(:map, _query),                 do: "text"
+    defp ecto_to_db({:map, _}, _query),            do: "text"
+    defp ecto_to_db(:time_usec, _query),           do: "time"
+    defp ecto_to_db(:utc_datetime, _query),        do: "datetime"
+    defp ecto_to_db(:utc_datetime_usec, _query),   do: "datetime"
+    defp ecto_to_db(:naive_datetime, _query),      do: "datetime"
+    defp ecto_to_db(:naive_datetime_usec, _query), do: "datetime"
+    defp ecto_to_db(other, _query),                do: Atom.to_string(other)
 
     defp error!(nil, message) do
       raise ArgumentError, message
diff --git a/lib/ecto/adapters/postgres/connection.ex b/lib/ecto/adapters/postgres/connection.ex
index 7771a86b..1c60612a 100644
--- a/lib/ecto/adapters/postgres/connection.ex
+++ b/lib/ecto/adapters/postgres/connection.ex
@@ -817,6 +817,21 @@ if Code.ensure_loaded?(Postgrex) do
 
     defp column_type({:array, type}, opts),
       do: [column_type(type, opts), "[]"]
+
+    defp column_type(type, _opts) when type in ~w(time utc_datetime naive_datetime)a,
+      do: [ecto_to_db(type), "(0)"]
+
+    defp column_type(type, opts) when type in ~w(time_usec utc_datetime_usec naive_datetime_usec)a do
+      precision = Keyword.get(opts, :precision)
+      type_name = ecto_to_db(type)
+
+      if precision do
+        [type_name, ?(, to_string(precision), ?)]
+      else
+        type_name
+      end
+    end
+
     defp column_type(type, opts) do
       size      = Keyword.get(opts, :size)
       precision = Keyword.get(opts, :precision)
@@ -925,18 +940,21 @@ if Code.ensure_loaded?(Postgrex) do
       :binary.replace(value, "'", "''", [:global])
     end
 
-    defp ecto_to_db({:array, t}),     do: [ecto_to_db(t), ?[, ?]]
-    defp ecto_to_db(:id),             do: "integer"
-    defp ecto_to_db(:serial),         do: "serial"
-    defp ecto_to_db(:bigserial),      do: "bigserial"
-    defp ecto_to_db(:binary_id),      do: "uuid"
-    defp ecto_to_db(:string),         do: "varchar"
-    defp ecto_to_db(:binary),         do: "bytea"
-    defp ecto_to_db(:map),            do: Application.fetch_env!(:ecto, :postgres_map_type)
-    defp ecto_to_db({:map, _}),       do: Application.fetch_env!(:ecto, :postgres_map_type)
-    defp ecto_to_db(:utc_datetime),   do: "timestamp"
-    defp ecto_to_db(:naive_datetime), do: "timestamp"
-    defp ecto_to_db(other),           do: Atom.to_string(other)
+    defp ecto_to_db({:array, t}),          do: [ecto_to_db(t), ?[, ?]]
+    defp ecto_to_db(:id),                  do: "integer"
+    defp ecto_to_db(:serial),              do: "serial"
+    defp ecto_to_db(:bigserial),           do: "bigserial"
+    defp ecto_to_db(:binary_id),           do: "uuid"
+    defp ecto_to_db(:string),              do: "varchar"
+    defp ecto_to_db(:binary),              do: "bytea"
+    defp ecto_to_db(:map),                 do: Application.fetch_env!(:ecto, :postgres_map_type)
+    defp ecto_to_db({:map, _}),            do: Application.fetch_env!(:ecto, :postgres_map_type)
+    defp ecto_to_db(:time_usec),           do: "time"
+    defp ecto_to_db(:utc_datetime),        do: "timestamp"
+    defp ecto_to_db(:utc_datetime_usec),   do: "timestamp"
+    defp ecto_to_db(:naive_datetime),      do: "timestamp"
+    defp ecto_to_db(:naive_datetime_usec), do: "timestamp"
+    defp ecto_to_db(other),                do: Atom.to_string(other)
 
     defp error!(nil, message) do
       raise ArgumentError, message
diff --git a/lib/ecto/poison.ex b/lib/ecto/poison.ex
index d4b4c52b..a39bfcd2 100644
--- a/lib/ecto/poison.ex
+++ b/lib/ecto/poison.ex
@@ -5,9 +5,23 @@ if Code.ensure_loaded?(Poison) do
 
   defimpl Poison.Encoder, for: Ecto.Association.NotLoaded do
     def encode(%{__owner__: owner, __field__: field}, _) do
-      raise "cannot encode association #{inspect field} from #{inspect owner} to " <>
-            "JSON because the association was not loaded. Please make sure you have " <>
-            "preloaded the association or remove it from the data to be encoded"
+      raise """
+      cannot encode association #{inspect field} from #{inspect owner} to \
+      JSON because the association was not loaded.
+
+      You can either preload the association:
+
+          Repo.preload(#{inspect owner}, #{inspect field})
+
+      Or choose to not encode the association when converting the struct \
+      to JSON by explicitly listing the JSON fields in your schema:
+
+          defmodule #{inspect owner} do
+            # ...
+
+            @derive {Poison.Encoder, only: [:name, :title, ...]}
+            schema ... do
+      """
     end
   end
 end
diff --git a/lib/ecto/schema.ex b/lib/ecto/schema.ex
index 9a943530..515b5d9d 100644
--- a/lib/ecto/schema.ex
+++ b/lib/ecto/schema.ex
@@ -147,16 +147,13 @@ defmodule Ecto.Schema do
   `:date`                 | `Date` |
   `:time`                 | `Time` |
   `:naive_datetime`       | `NaiveDateTime` |
+  `:naive_datetime_usec`  | `NaiveDateTime` |
   `:utc_datetime`         | `DateTime` |
+  `:utc_datetime_usec`    | `DateTime` |
 
   **Note:** For the `{:array, inner_type}` and `{:map, inner_type}` type,
   replace `inner_type` with one of the valid types, such as `:string`.
 
-  Timestamps are typically represented by `:naive_datetime` or
-  `:utc_datetime`. The naive datetime uses Elixir's `NaiveDateTime` which
-  has no time zone information while `:utc_datetime` uses a `DateTime` and
-  expects the time zone to be set to UTC.
-
   ### Custom types
 
   Besides providing primitive types, Ecto allows custom types to be
@@ -176,6 +173,33 @@ defmodule Ecto.Schema do
   `virtual: true` option. These fields are not persisted to the database
   and can optionally not be type checked by declaring type `:any`.
 
+  ### The datetime types
+
+  Four different datetime primitive types are available:
+
+    * `naive_datetime` - has a precision of 0 microseconds and casts values
+      to Elixir's `NaiveDateTime` struct which has no timezone information.
+
+    * `naive_datetime_usec` - has a default precision of 6 microseconds and
+      also casts values to `NaiveDateTime` with no timezone information.
+
+    * `utc_datetime` - has a precision of 0 microseconds and casts values to
+      to Elixir's `DateTime` struct and expects the time zone to be set to UTC.
+
+    * `utc_datetime_usec` has a default precision of 6 microseconds and also
+      casts values to `DateTime` expecting the time zone be set to UTC.
+
+  Having these different types allows developers to choose a type that will
+  be compatible with the database and your project's precision requirements.
+  For example, some older versions of MySQL do not support microseconds in
+  datetime fields.
+
+  When choosing what datetime type to work with, keep in mind that Elixir
+  functions like `NaiveDateTime.utc_now/0` have a default precision of 6.
+  Casting a value with a precision greater than 0 to a non-`usec` type will
+  truncate all microseconds and set the precision to 0, making the raw utc_now
+  value ≠ to the cast value.
+
   ### The map type
 
   The map type allows developers to store an Elixir map directly
@@ -1440,9 +1464,19 @@ defmodule Ecto.Schema do
   def __timestamps__(:naive_datetime) do
     %{NaiveDateTime.utc_now() | microsecond: {0, 0}}
   end
+
+  def __timestamps__(:naive_datetime_usec) do
+    NaiveDateTime.utc_now()
+  end
+
   def __timestamps__(:utc_datetime) do
     DateTime.from_unix!(System.system_time(:seconds), :seconds)
   end
+
+  def __timestamps__(:utc_datetime_usec) do
+    DateTime.from_unix!(System.system_time(:microseconds), :microseconds)
+  end
+
   def __timestamps__(type) do
     type.from_unix!(System.system_time(:microseconds), :microseconds)
   end
diff --git a/lib/ecto/type.ex b/lib/ecto/type.ex
index ce825d9c..76524be8 100644
--- a/lib/ecto/type.ex
+++ b/lib/ecto/type.ex
@@ -10,21 +10,21 @@ defmodule Ecto.Type do
 
   ## Example
 
-  Imagine you want to store an URI struct as part of a schema in an 
-  url-shortening service. There isn't an Ecto field type to support 
+  Imagine you want to store an URI struct as part of a schema in an
+  url-shortening service. There isn't an Ecto field type to support
   that value at runtime, therefore a custom one is needed.
 
-  You also want to query not only by the full url, but for example 
+  You also want to query not only by the full url, but for example
   by specific ports used. This is possible by putting the URI data
-  into a map field instead of just storing the plain 
+  into a map field instead of just storing the plain
   string representation.
 
       from s in ShortUrl,
         where: fragment("?->>? ILIKE ?", s.original_url, "port", "443")
 
-  So the custom type does need to handle the conversion from 
-  external data to runtime data (`c:cast/1`) as well as 
-  transforming that runtime data into the `:map` Ecto native type and 
+  So the custom type does need to handle the conversion from
+  external data to runtime data (`c:cast/1`) as well as
+  transforming that runtime data into the `:map` Ecto native type and
   back (`c:dump/1` and `c:load/1`).
 
       defmodule EctoURI do
@@ -45,10 +45,10 @@ defmodule Ecto.Type do
 
         # When loading data from the database, we are guaranteed to
         # receive a map (as databases are strict) and we will
-        # just put the data back into an URI struct to be stored 
+        # just put the data back into an URI struct to be stored
         # in the loaded schema struct.
         def load(data) when is_map(data) do
-          data = 
+          data =
             for {key, val} <- data do
               {String.to_existing_atom(key), val}
             end
@@ -87,10 +87,16 @@ defmodule Ecto.Type do
 
   @typep base      :: :integer | :float | :boolean | :string | :map |
                       :binary | :decimal | :id | :binary_id |
-                      :utc_datetime  | :naive_datetime | :date | :time | :any
+                      :utc_datetime | :naive_datetime | :date | :time | :any |
+                      :utc_datetime_usec | :naive_datetime_usec | :time_usec
   @typep composite :: {:array, t} | {:map, t} | {:embed, Ecto.Embedded.t} | {:in, t}
 
-  @base      ~w(integer float boolean string binary decimal datetime utc_datetime naive_datetime date time id binary_id map any)a
+  @base ~w(
+    integer float boolean string map
+    binary decimal id binary_id
+    utc_datetime naive_datetime date time any
+    utc_datetime_usec naive_datetime_usec time_usec
+  )a
   @composite ~w(array map in embed)a
 
   @doc """
@@ -324,18 +330,34 @@ defmodule Ecto.Type do
   end
 
   def dump(:time, %Time{} = time, _dumper) do
+    {:ok, zerofy_microsecond(time)}
+  end
+
+  def dump(:time_usec, %Time{} = time, _dumper) do
     {:ok, time}
   end
 
   def dump(:naive_datetime, %NaiveDateTime{} = naive_datetime, _dumper) do
+    {:ok, zerofy_microsecond(naive_datetime)}
+  end
+
+  def dump(:naive_datetime_usec, %NaiveDateTime{} = naive_datetime, _dumper) do
     {:ok, naive_datetime}
   end
 
-  def dump(:utc_datetime, %DateTime{time_zone: time_zone} = datetime, _loader) do
+  def dump(:utc_datetime, %DateTime{time_zone: time_zone} = datetime, _dumper) do
     if time_zone != "Etc/UTC" do
       message = ":utc_datetime expects the time zone to be \"Etc/UTC\", got `#{inspect(datetime)}`"
       raise ArgumentError, message
     end
+    {:ok, datetime |> DateTime.to_naive() |> zerofy_microsecond()}
+  end
+
+  def dump(:utc_datetime_usec, %DateTime{time_zone: time_zone} = datetime, _dumper) do
+    if time_zone != "Etc/UTC" do
+      message = ":utc_datetime_usec expects the time zone to be \"Etc/UTC\", got `#{inspect(datetime)}`"
+      raise ArgumentError, message
+    end
     {:ok, DateTime.to_naive(datetime)}
   end
 
@@ -423,14 +445,26 @@ defmodule Ecto.Type do
     {:ok, zerofy_microsecond(time)}
   end
 
+  def load(:time_usec, %Time{} = time, _loader) do
+    {:ok, time}
+  end
+
   def load(:naive_datetime, %NaiveDateTime{} = naive_datetime, _loader) do
     {:ok, zerofy_microsecond(naive_datetime)}
   end
 
+  def load(:naive_datetime_usec, %NaiveDateTime{} = naive_datetime, _loader) do
+    {:ok, naive_datetime}
+  end
+
   def load(:utc_datetime, %NaiveDateTime{} = naive_datetime, _loader) do
     naive_datetime |> zerofy_microsecond |> DateTime.from_naive("Etc/UTC")
   end
 
+  def load(:utc_datetime_usec, %NaiveDateTime{} = naive_datetime, _loader) do
+     DateTime.from_naive(naive_datetime, "Etc/UTC")
+  end
+
   def load(type, value, _loader) do
     cond do
       not primitive?(type) ->
@@ -594,6 +628,10 @@ defmodule Ecto.Type do
     end
   end
 
+  def cast(:time_usec, term) do
+    cast_time(term)
+  end
+
   def cast(:naive_datetime, term) do
     case cast_naive_datetime(term) do
       {:ok, naive_datetime} -> {:ok, zerofy_microsecond(naive_datetime)}
@@ -601,6 +639,10 @@ defmodule Ecto.Type do
     end
   end
 
+  def cast(:naive_datetime_usec, term) do
+    cast_naive_datetime(term)
+  end
+
   def cast(:utc_datetime, term) do
     case cast_utc_datetime(term) do
       {:ok, utc_datetime} -> {:ok, zerofy_microsecond(utc_datetime)}
@@ -608,6 +650,10 @@ defmodule Ecto.Type do
     end
   end
 
+  def cast(:utc_datetime_usec, term) do
+    cast_utc_datetime(term)
+  end
+
   def cast(type, term) when type in [:id, :integer] and is_binary(term) do
     case Integer.parse(term) do
       {int, ""} -> {:ok, int}
diff --git a/lib/ecto/uuid.ex b/lib/ecto/uuid.ex
index 3479c1a7..b563e674 100644
--- a/lib/ecto/uuid.ex
+++ b/lib/ecto/uuid.ex
@@ -146,9 +146,6 @@ defmodule Ecto.UUID do
     raise ArgumentError, "trying to load string UUID as Ecto.UUID: #{inspect string}. " <>
                          "Maybe you wanted to declare :uuid as your database field?"
   end
-  def load(%Ecto.Query.Tagged{type: :uuid, value: uuid}) do
-    encode(uuid)
-  end
   def load(_), do: :error
 
   @doc """


Test patch:
diff --git a/integration_test/cases/interval.exs b/integration_test/cases/interval.exs
index 09e36912..7c027e62 100644
--- a/integration_test/cases/interval.exs
+++ b/integration_test/cases/interval.exs
@@ -1,6 +1,7 @@
 defmodule Ecto.Integration.IntervalTest do
   use Ecto.Integration.Case, async: Application.get_env(:ecto, :async_integration_tests, true)
 
+  alias Ecto.Integration.Article
   alias Ecto.Integration.{Post, User}
   alias Ecto.Integration.TestRepo
   import Ecto.Query
@@ -286,6 +287,8 @@ defmodule Ecto.Integration.IntervalTest do
     {:ok, datetime} = DateTime.from_naive(~N[2015-01-01 02:00:00], "Etc/UTC")
     dec = Decimal.new(1)
 
+    assert [^datetime] =
+           TestRepo.all(from p in User, select: datetime_add(type(^datetime, :utc_datetime), 0, "year"))
     assert [^datetime] =
            TestRepo.all(from p in User, select: datetime_add(p.inserted_at, 1, "year"))
     assert [^datetime] =
@@ -297,4 +300,39 @@ defmodule Ecto.Integration.IntervalTest do
     assert [^datetime] =
            TestRepo.all(from p in User, select: datetime_add(p.inserted_at, ^dec, "year"))
   end
+
+  test "datetime_add with naive_datetime_usec" do
+    TestRepo.insert!(%Article{published_at: ~N[2014-01-01 02:00:00.000001]})
+    dec = Decimal.new(1500)
+
+    assert [~N[2014-01-01 02:00:00.001501]] =
+           TestRepo.all(from p in Article, select: datetime_add(p.published_at, 1500, "microsecond"))
+    assert [~N[2014-01-01 02:00:00.001501]] =
+           TestRepo.all(from p in Article, select: datetime_add(p.published_at, 1500.0, "microsecond"))
+    assert [~N[2014-01-01 02:00:00.001501]] =
+           TestRepo.all(from p in Article, select: datetime_add(p.published_at, ^1500, "microsecond"))
+    assert [~N[2014-01-01 02:00:00.001501]] =
+           TestRepo.all(from p in Article, select: datetime_add(p.published_at, ^1500.0, "microsecond"))
+    assert [~N[2014-01-01 02:00:00.001501]] =
+           TestRepo.all(from p in Article, select: datetime_add(p.published_at, ^dec, "microsecond"))
+  end
+
+  test "datetime_add with utc_datetime_usec" do
+    {:ok, datetime} = DateTime.from_naive(~N[2014-01-01 02:00:00.000001], "Etc/UTC")
+    TestRepo.insert!(%Article{submitted_at: datetime})
+
+    {:ok, datetime} = DateTime.from_naive(~N[2014-01-01 02:00:00.001501], "Etc/UTC")
+    dec = Decimal.new(1500)
+
+    assert [^datetime] =
+           TestRepo.all(from p in Article, select: datetime_add(p.submitted_at, 1500, "microsecond"))
+    assert [^datetime] =
+           TestRepo.all(from p in Article, select: datetime_add(p.submitted_at, 1500.0, "microsecond"))
+    assert [^datetime] =
+           TestRepo.all(from p in Article, select: datetime_add(p.submitted_at, ^1500, "microsecond"))
+    assert [^datetime] =
+           TestRepo.all(from p in Article, select: datetime_add(p.submitted_at, ^1500.0, "microsecond"))
+    assert [^datetime] =
+           TestRepo.all(from p in Article, select: datetime_add(p.submitted_at, ^dec, "microsecond"))
+  end
 end
diff --git a/integration_test/support/migration.exs b/integration_test/support/migration.exs
index 8cd1e7e8..4e960d4e 100644
--- a/integration_test/support/migration.exs
+++ b/integration_test/support/migration.exs
@@ -110,5 +110,10 @@ defmodule Ecto.Integration.Migration do
     end
 
     create unique_index(:posts_users_composite_pk, [:post_id, :user_id])
+
+    create table(:articles) do
+      add :published_at, :naive_datetime_usec
+      add :submitted_at, :utc_datetime_usec
+    end
   end
 end
diff --git a/integration_test/support/schemas.exs b/integration_test/support/schemas.exs
index f1e262af..b2495a24 100644
--- a/integration_test/support/schemas.exs
+++ b/integration_test/support/schemas.exs
@@ -278,3 +278,18 @@ defmodule Ecto.Integration.PostUserCompositePk do
     timestamps()
   end
 end
+
+defmodule Ecto.Integration.Article do
+  @moduledoc """
+  This module is used to test:
+
+    * usec datetime types
+
+  """
+  use Ecto.Integration.Schema
+
+  schema "articles" do
+    field :published_at, :naive_datetime_usec
+    field :submitted_at, :utc_datetime_usec
+  end
+end
diff --git a/test/ecto/adapters/mysql_test.exs b/test/ecto/adapters/mysql_test.exs
index 0508464d..91468c84 100644
--- a/test/ecto/adapters/mysql_test.exs
+++ b/test/ecto/adapters/mysql_test.exs
@@ -682,6 +682,84 @@ defmodule Ecto.Adapters.MySQLTest do
     assert execute_ddl(create) == [~s|CREATE TABLE `posts` (`a` text DEFAULT '{"foo":"bar","baz":"boom"}') ENGINE = INNODB|]
   end
 
+  test "create table with time columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :time, [precision: 3]},
+               {:add, :submitted_at, :time, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` time,
+    `submitted_at` time)
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
+  test "create table with time_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :time_usec, [precision: 3]},
+               {:add, :submitted_at, :time_usec, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` time(3),
+    `submitted_at` time(6))
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
+  test "create table with utc_datetime columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :utc_datetime, [precision: 3]},
+               {:add, :submitted_at, :utc_datetime, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` datetime,
+    `submitted_at` datetime)
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
+  test "create table with utc_datetime_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :utc_datetime_usec, [precision: 3]},
+               {:add, :submitted_at, :utc_datetime_usec, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` datetime(3),
+    `submitted_at` datetime(6))
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
+  test "create table with naive_datetime columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :naive_datetime, [precision: 3]},
+               {:add, :submitted_at, :naive_datetime, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` datetime,
+    `submitted_at` datetime)
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
+  test "create table with naive_datetime_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :naive_datetime_usec, [precision: 3]},
+               {:add, :submitted_at, :naive_datetime_usec, []}]}
+
+    assert execute_ddl(create) == ["""
+    CREATE TABLE `posts`
+    (`published_at` datetime(3),
+    `submitted_at` datetime(6))
+    ENGINE = INNODB
+    """ |> remove_newlines]
+  end
+
   test "drop table" do
     drop = {:drop, table(:posts)}
     assert execute_ddl(drop) == [~s|DROP TABLE `posts`|]
diff --git a/test/ecto/adapters/postgres_test.exs b/test/ecto/adapters/postgres_test.exs
index 593e1feb..780eed3d 100644
--- a/test/ecto/adapters/postgres_test.exs
+++ b/test/ecto/adapters/postgres_test.exs
@@ -829,7 +829,7 @@ defmodule Ecto.Adapters.PostgresTest do
               [{:add, :id, :serial, [primary_key: true]},
                {:add, :created_at, :naive_datetime, []}]}
     assert execute_ddl(create) ==
-      [~s|CREATE TABLE "posts" ("id" serial, "created_at" timestamp, PRIMARY KEY ("id")) WITH FOO=BAR|]
+      [~s|CREATE TABLE "posts" ("id" serial, "created_at" timestamp(0), PRIMARY KEY ("id")) WITH FOO=BAR|]
   end
 
   test "create table with composite key" do
@@ -870,6 +870,54 @@ defmodule Ecto.Adapters.PostgresTest do
     assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("a" jsonb DEFAULT '{"foo":"bar","baz":"boom"}')|]
   end
 
+  test "create table with time columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :time, [precision: 3]},
+               {:add, :submitted_at, :time, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" time(0), "submitted_at" time(0))|]
+  end
+
+  test "create table with time_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :time_usec, [precision: 3]},
+               {:add, :submitted_at, :time_usec, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" time(3), "submitted_at" time)|]
+  end
+
+  test "create table with utc_datetime columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :utc_datetime, [precision: 3]},
+               {:add, :submitted_at, :utc_datetime, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" timestamp(0), "submitted_at" timestamp(0))|]
+  end
+
+  test "create table with utc_datetime_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :utc_datetime_usec, [precision: 3]},
+               {:add, :submitted_at, :utc_datetime_usec, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" timestamp(3), "submitted_at" timestamp)|]
+  end
+
+  test "create table with naive_datetime columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :naive_datetime, [precision: 3]},
+               {:add, :submitted_at, :naive_datetime, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" timestamp(0), "submitted_at" timestamp(0))|]
+  end
+
+  test "create table with naive_datetime_usec columns" do
+    create = {:create, table(:posts),
+              [{:add, :published_at, :naive_datetime_usec, [precision: 3]},
+               {:add, :submitted_at, :naive_datetime_usec, []}]}
+
+    assert execute_ddl(create) == [~s|CREATE TABLE "posts" ("published_at" timestamp(3), "submitted_at" timestamp)|]
+  end
+
   test "drop table" do
     drop = {:drop, table(:posts)}
     assert execute_ddl(drop) == [~s|DROP TABLE "posts"|]
diff --git a/test/ecto/repo/autogenerate_test.exs b/test/ecto/repo/autogenerate_test.exs
index f759a28c..38776ff5 100644
--- a/test/ecto/repo/autogenerate_test.exs
+++ b/test/ecto/repo/autogenerate_test.exs
@@ -22,6 +22,38 @@ defmodule Ecto.Repo.AutogenerateTest do
     end
   end
 
+  defmodule NaiveMod do
+    use Ecto.Schema
+
+    schema "naive_mod" do
+      timestamps(type: :naive_datetime)
+    end
+  end
+
+  defmodule NaiveUsecMod do
+    use Ecto.Schema
+
+    schema "naive_usec_mod" do
+      timestamps(type: :naive_datetime_usec)
+    end
+  end
+
+  defmodule UtcMod do
+    use Ecto.Schema
+
+    schema "utc_mod" do
+      timestamps(type: :utc_datetime)
+    end
+  end
+
+  defmodule UtcUsecMod do
+    use Ecto.Schema
+
+    schema "utc_usec_mod" do
+      timestamps(type: :utc_datetime_usec)
+    end
+  end
+
   ## Autogenerate
 
   @uuid "30313233-3435-4637-9839-616263646566"
@@ -51,8 +83,8 @@ defmodule Ecto.Repo.AutogenerateTest do
 
   test "sets inserted_at and updated_at values" do
     default = TestRepo.insert!(%Company{})
-    assert %NaiveDateTime{} = default.inserted_at
-    assert %NaiveDateTime{} = default.updated_at
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.inserted_at
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.updated_at
     assert_received {:insert, _}
 
     # No change
@@ -74,7 +106,7 @@ defmodule Ecto.Repo.AutogenerateTest do
     changeset = Ecto.Changeset.change(%Company{id: 1})
     default = TestRepo.update!(changeset, force: true)
     refute default.inserted_at
-    assert %NaiveDateTime{} = default.updated_at
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.updated_at
     assert_received {:update, _}
   end
 
@@ -93,11 +125,51 @@ defmodule Ecto.Repo.AutogenerateTest do
 
   test "sets custom inserted_at and updated_at values" do
     default = TestRepo.insert!(%Manager{})
-    assert %DateTime{time_zone: "Etc/UTC"} = default.created_on
-    assert %DateTime{time_zone: "Etc/UTC"} = default.updated_on
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.created_on
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.updated_on
 
     default = TestRepo.update!(%Manager{id: 1} |> Ecto.Changeset.change, force: true)
     refute default.created_on
-    assert %DateTime{time_zone: "Etc/UTC"} = default.updated_on
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.updated_on
+  end
+
+  test "sets the timestamps type to naive_datetime" do
+    default = TestRepo.insert!(%NaiveMod{})
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.inserted_at
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.updated_at
+
+    default = TestRepo.update!(%NaiveMod{id: 1} |> Ecto.Changeset.change, force: true)
+    refute default.inserted_at
+    assert %NaiveDateTime{microsecond: {0, 0}} = default.updated_at
+  end
+
+  test "sets the timestamps type to naive_datetime_usec" do
+    default = TestRepo.insert!(%NaiveUsecMod{})
+    assert %NaiveDateTime{microsecond: {_, 6}} = default.inserted_at
+    assert %NaiveDateTime{microsecond: {_, 6}} = default.updated_at
+
+    default = TestRepo.update!(%NaiveUsecMod{id: 1} |> Ecto.Changeset.change, force: true)
+    refute default.inserted_at
+    assert %NaiveDateTime{microsecond: {_, 6}} = default.updated_at
+  end
+
+  test "sets the timestamps type to utc_datetime" do
+    default = TestRepo.insert!(%UtcMod{})
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.inserted_at
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.updated_at
+
+    default = TestRepo.update!(%UtcMod{id: 1} |> Ecto.Changeset.change, force: true)
+    refute default.inserted_at
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {0, 0}} = default.updated_at
+  end
+
+  test "sets the timestamps type to utc_datetime_usec" do
+    default = TestRepo.insert!(%UtcUsecMod{})
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {_, 6}} = default.inserted_at
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {_, 6}} = default.updated_at
+
+    default = TestRepo.update!(%UtcUsecMod{id: 1} |> Ecto.Changeset.change, force: true)
+    refute default.inserted_at
+    assert %DateTime{time_zone: "Etc/UTC", microsecond: {_, 6}} = default.updated_at
   end
 end
diff --git a/test/ecto/type_test.exs b/test/ecto/type_test.exs
index 17a7de5a..a15423bb 100644
--- a/test/ecto/type_test.exs
+++ b/test/ecto/type_test.exs
@@ -201,6 +201,18 @@ defmodule Ecto.TypeTest do
     assert Ecto.Type.cast(:date, "2015-12-31 00:00:00") == {:ok, @date}
   end
 
+  test "dump :date" do
+    assert Ecto.Type.dump(:date, @date) == {:ok, @date}
+    assert Ecto.Type.dump(:date, @leap_date) == {:ok, @leap_date}
+    assert Ecto.Type.dump(:date, @date_unix_epoch) ==  {:ok, @date_unix_epoch}
+  end
+
+  test "load :date" do
+    assert Ecto.Type.load(:date, @date) == {:ok, @date}
+    assert Ecto.Type.load(:date, @leap_date) == {:ok, @leap_date}
+    assert Ecto.Type.load(:date, @date_unix_epoch) ==  {:ok, @date_unix_epoch}
+  end
+
   @time ~T[23:50:07]
   @time_zero ~T[23:50:00]
   @time_usec ~T[23:50:07.030000]
@@ -248,6 +260,72 @@ defmodule Ecto.TypeTest do
            :error
   end
 
+  test "dump :time" do
+    assert Ecto.Type.dump(:time, @time) == {:ok, @time}
+    assert Ecto.Type.dump(:time, @time_usec) == {:ok, @time}
+    assert Ecto.Type.dump(:time, @time_zero) ==  {:ok, @time_zero}
+  end
+
+  test "load :time" do
+    assert Ecto.Type.load(:time, @time) == {:ok, @time}
+    assert Ecto.Type.load(:time, @time_usec) == {:ok, @time}
+    assert Ecto.Type.load(:time, @time_zero) ==  {:ok, @time_zero}
+  end
+
+  describe "time_usec type" do
+    test "cast :time_usec from Time" do
+      assert Ecto.Type.cast(:time_usec, @time) == {:ok, @time}
+      assert Ecto.Type.cast(:time_usec, @time_usec) == {:ok, @time_usec}
+      assert Ecto.Type.cast(:time_usec, @time_zero) ==  {:ok, @time_zero}
+    end
+
+    test "cast :time_usec from binary" do
+      assert Ecto.Type.cast(:time_usec, "23:50:07") == {:ok, @time}
+      assert Ecto.Type.cast(:time_usec, "23:50:07Z") == {:ok, @time}
+      assert Ecto.Type.cast(:time_usec, "23:50:07.030000") == {:ok, @time_usec}
+      assert Ecto.Type.cast(:time_usec, "23:50:07.030000Z") == {:ok, @time_usec}
+
+      assert Ecto.Type.cast(:time_usec, "24:01:01") == :error
+      assert Ecto.Type.cast(:time_usec, "00:61:00") == :error
+      assert Ecto.Type.cast(:time_usec, "00:00:61") == :error
+      assert Ecto.Type.cast(:time_usec, "00:00:009") == :error
+      assert Ecto.Type.cast(:time_usec, "00:00:00.A00") == :error
+    end
+
+    test "cast :time_usec from map" do
+      assert Ecto.Type.cast(:time_usec, %{"hour" => "23", "minute" => "50", "second" => "07"}) == {:ok, @time}
+      assert Ecto.Type.cast(:time_usec, %{hour: 23, minute: 50, second: 07}) == {:ok, @time}
+      assert Ecto.Type.cast(:time_usec, %{"hour" => "", "minute" => ""}) == {:ok, nil}
+      assert Ecto.Type.cast(:time_usec, %{hour: nil, minute: nil}) == {:ok, nil}
+      assert Ecto.Type.cast(:time_usec, %{"hour" => "23", "minute" => "50"}) == {:ok, @time_zero}
+      assert Ecto.Type.cast(:time_usec, %{hour: 23, minute: 50}) == {:ok, @time_zero}
+      assert Ecto.Type.cast(:time_usec, %{hour: 23, minute: 50, second: 07, microsecond: 30_000}) == {:ok, @time_usec}
+      assert Ecto.Type.cast(:time_usec, %{"hour" => 23, "minute" => 50, "second" => 07, "microsecond" => 30_000}) == {:ok, @time_usec}
+      assert Ecto.Type.cast(:time_usec, %{"hour" => "", "minute" => "50"}) == :error
+      assert Ecto.Type.cast(:time_usec, %{hour: 23, minute: nil}) == :error
+    end
+
+    test "cast :time_usec from NaiveDateTime" do
+      assert Ecto.Type.cast(:time_usec, ~N[2016-11-11 23:30:10]) == {:ok, ~T[23:30:10]}
+    end
+
+    test "cast :time_usec from Date" do
+      assert Ecto.Type.cast(:time_usec, ~D[2016-11-11]) == :error
+    end
+
+    test "dump :time_usec" do
+      assert Ecto.Type.dump(:time_usec, @time) == {:ok, @time}
+      assert Ecto.Type.dump(:time_usec, @time_usec) == {:ok, @time_usec}
+      assert Ecto.Type.dump(:time_usec, @time_zero) ==  {:ok, @time_zero}
+    end
+
+    test "load :time_usec" do
+      assert Ecto.Type.load(:time_usec, @time) == {:ok, @time}
+      assert Ecto.Type.load(:time_usec, @time_usec) == {:ok, @time_usec}
+      assert Ecto.Type.load(:time_usec, @time_zero) ==  {:ok, @time_zero}
+    end
+  end
+
   @datetime ~N[2015-01-23 23:50:07]
   @datetime_zero ~N[2015-01-23 23:50:00]
   @datetime_usec ~N[2015-01-23 23:50:07.008000]
@@ -311,6 +389,99 @@ defmodule Ecto.TypeTest do
            :error
   end
 
+  test "dump :naive_datetime" do
+    assert Ecto.Type.dump(:naive_datetime, @datetime) == {:ok, @datetime}
+    assert Ecto.Type.dump(:naive_datetime, @datetime_zero) == {:ok, @datetime_zero}
+    assert Ecto.Type.dump(:naive_datetime, @datetime_usec) == {:ok, @datetime}
+    assert Ecto.Type.dump(:naive_datetime, @datetime_leapyear) == {:ok, @datetime_leapyear}
+  end
+
+  test "load :naive_datetime" do
+    assert Ecto.Type.load(:naive_datetime, @datetime) == {:ok, @datetime}
+    assert Ecto.Type.load(:naive_datetime, @datetime_zero) == {:ok, @datetime_zero}
+    assert Ecto.Type.load(:naive_datetime, @datetime_usec) == {:ok, @datetime}
+    assert Ecto.Type.load(:naive_datetime, @datetime_leapyear) == {:ok, @datetime_leapyear}
+  end
+
+  describe "naive_datetime_usec type" do
+    test "cast :naive_datetime_usec from NaiveDateTime" do
+      assert Ecto.Type.cast(:naive_datetime_usec, @datetime) == {:ok, @datetime}
+      assert Ecto.Type.cast(:naive_datetime_usec, @datetime_usec) == {:ok, @datetime_usec}
+      assert Ecto.Type.cast(:naive_datetime_usec, @datetime_leapyear) == {:ok, @datetime_leapyear}
+    end
+
+    test "cast :naive_datetime_usec from binary" do
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23 23:50:07") == {:ok, @datetime}
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23T23:50:07") == {:ok, @datetime}
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23T23:50:07Z") == {:ok, @datetime}
+      assert Ecto.Type.cast(:naive_datetime_usec, "2000-02-29T23:50:07") == {:ok, @datetime_leapyear}
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23T23:50:07.008000") == {:ok, @datetime_usec}
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23T23:50:07.008000Z") == {:ok, @datetime_usec}
+
+      assert Ecto.Type.cast(:naive_datetime_usec, "2015-01-23P23:50:07") == :error
+    end
+
+    test "cast :naive_datetime_usec from map" do
+      term = %{"year" => "2015", "month" => "1", "day" => "23", "hour" => "23", "minute" => "50", "second" => "07"}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50, second: 07}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime}
+
+      term = %{"year" => "", "month" => "", "day" => "", "hour" => "", "minute" => ""}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, nil}
+
+      term = %{year: nil, month: nil, day: nil, hour: nil, minute: nil}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, nil}
+
+      term = %{"year" => "2015", "month" => "1", "day" => "23", "hour" => "23", "minute" => "50"}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime_zero}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime_zero}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50, second: 07, microsecond: 8_000}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime_usec}
+
+      term = %{
+        "year" => 2015, "month" => 1, "day" => 23,
+        "hour" => 23, "minute" => 50, "second" => 07, "microsecond" => 8_000
+      }
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == {:ok, @datetime_usec}
+
+      term = %{
+        "year" => "2015", "month" => "1", "day" => "23",
+        "hour" => "", "minute" => "50"
+      }
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == :error
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: nil}
+      assert Ecto.Type.cast(:naive_datetime_usec, term) == :error
+    end
+
+    test "cast :naive_datetime_usec from DateTime" do
+      assert Ecto.Type.cast(:naive_datetime_usec, DateTime.from_unix!(10, :seconds)) == {:ok, ~N[1970-01-01 00:00:10]}
+    end
+
+    test "cast :naive_datetime_usec from Time" do
+      assert Ecto.Type.cast(:naive_datetime_usec, ~T[23:50:07]) == :error
+    end
+
+    test "dump :naive_datetime_usec" do
+      assert Ecto.Type.dump(:naive_datetime_usec, @datetime) == {:ok, @datetime}
+      assert Ecto.Type.dump(:naive_datetime_usec, @datetime_zero) == {:ok, @datetime_zero}
+      assert Ecto.Type.dump(:naive_datetime_usec, @datetime_usec) == {:ok, @datetime_usec}
+      assert Ecto.Type.dump(:naive_datetime_usec, @datetime_leapyear) == {:ok, @datetime_leapyear}
+    end
+
+    test "load :naive_datetime_usec" do
+      assert Ecto.Type.load(:naive_datetime_usec, @datetime) == {:ok, @datetime}
+      assert Ecto.Type.load(:naive_datetime_usec, @datetime_zero) == {:ok, @datetime_zero}
+      assert Ecto.Type.load(:naive_datetime_usec, @datetime_usec) == {:ok, @datetime_usec}
+      assert Ecto.Type.load(:naive_datetime_usec, @datetime_leapyear) == {:ok, @datetime_leapyear}
+    end
+  end
+
   @datetime DateTime.from_unix!(1422057007, :seconds)
   @datetime_zero DateTime.from_unix!(1422057000, :seconds)
   @datetime_usec DateTime.from_unix!(1422057007008000, :microseconds)
@@ -378,4 +549,103 @@ defmodule Ecto.TypeTest do
     assert Ecto.Type.cast(:utc_datetime, ~T[12:23:34]) ==
            :error
   end
+
+  test "dump :utc_datetime" do
+    assert Ecto.Type.dump(:utc_datetime, @datetime) == {:ok, ~N[2015-01-23 23:50:07]}
+    assert Ecto.Type.dump(:utc_datetime, @datetime_zero) == {:ok, ~N[2015-01-23 23:50:00]}
+    assert Ecto.Type.dump(:utc_datetime, @datetime_usec) == {:ok, ~N[2015-01-23 23:50:07]}
+    assert Ecto.Type.dump(:utc_datetime, @datetime_leapyear) == {:ok, ~N[2000-02-29 23:50:07]}
+  end
+
+  test "load :utc_datetime" do
+    assert Ecto.Type.load(:utc_datetime, ~N[2015-01-23 23:50:07]) == {:ok, @datetime}
+    assert Ecto.Type.load(:utc_datetime, ~N[2015-01-23 23:50:00]) == {:ok, @datetime_zero}
+    assert Ecto.Type.load(:utc_datetime, ~N[2015-01-23 23:50:07.008000]) == {:ok, @datetime}
+    assert Ecto.Type.load(:utc_datetime, ~N[2000-02-29 23:50:07]) == {:ok, @datetime_leapyear}
+  end
+
+  describe "utc_datetime_usec type" do
+    test "cast :utc_datetime_usec from DateTime" do
+      assert Ecto.Type.cast(:utc_datetime_usec, @datetime) == {:ok, @datetime}
+      assert Ecto.Type.cast(:utc_datetime_usec, @datetime_usec) == {:ok, @datetime_usec}
+      assert Ecto.Type.cast(:utc_datetime_usec, @datetime_leapyear) == {:ok, @datetime_leapyear}
+    end
+
+    test "cast :utc_datetime_usec from binary" do
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23 23:50:07") == {:ok, @datetime}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23T23:50:07") == {:ok, @datetime}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23T23:50:07Z") == {:ok, @datetime}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-24T09:50:07+10:00") == {:ok, @datetime}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2000-02-29T23:50:07") == {:ok, @datetime_leapyear}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23T23:50:07.008000") == {:ok, @datetime_usec}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23T23:50:07.008000Z") == {:ok, @datetime_usec}
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23T17:50:07.008000-06:00") == {:ok, @datetime_usec}
+
+      assert Ecto.Type.cast(:utc_datetime_usec, "2015-01-23P23:50:07") == :error
+    end
+
+    test "cast :utc_datetime_usec from map" do
+      term = %{
+        "year" => "2015", "month" => "1", "day" => "23",
+        "hour" => "23", "minute" => "50", "second" => "07"
+      }
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50, second: 07}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime}
+
+      term = %DateTime{
+        calendar: Calendar.ISO, year: 2015, month: 1, day: 24,
+        hour: 9, minute: 50, second: 7, microsecond: {0, 0},
+        std_offset: 0, utc_offset: 36000,
+        time_zone: "Etc/GMT-10", zone_abbr: "+10"
+      }
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime}
+
+      term = %{"year" => "", "month" => "", "day" => "", "hour" => "", "minute" => ""}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, nil}
+
+      term = %{year: nil, month: nil, day: nil, hour: nil, minute: nil}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, nil}
+
+      term = %{"year" => "2015", "month" => "1", "day" => "23", "hour" => "23", "minute" => "50"}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime_zero}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime_zero}
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: 50, second: 07, microsecond: 8_000}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime_usec}
+
+      term = %{
+        "year" => 2015, "month" => 1, "day" => 23,
+        "hour" => 23, "minute" => 50, "second" => 07, "microsecond" => 8_000
+      }
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == {:ok, @datetime_usec}
+
+      term = %{"year" => "2015", "month" => "1", "day" => "23", "hour" => "", "minute" => "50"}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == :error
+
+      term = %{year: 2015, month: 1, day: 23, hour: 23, minute: nil}
+      assert Ecto.Type.cast(:utc_datetime_usec, term) == :error
+    end
+
+    test "cast :utc_datetime_usec from Time" do
+      assert Ecto.Type.cast(:utc_datetime_usec, ~T[12:23:34]) == :error
+    end
+
+    test "dump :utc_datetime_usec" do
+      assert Ecto.Type.dump(:utc_datetime_usec, @datetime) == {:ok, ~N[2015-01-23 23:50:07]}
+      assert Ecto.Type.dump(:utc_datetime_usec, @datetime_zero) == {:ok, ~N[2015-01-23 23:50:00]}
+      assert Ecto.Type.dump(:utc_datetime_usec, @datetime_usec) == {:ok, ~N[2015-01-23 23:50:07.008000]}
+      assert Ecto.Type.dump(:utc_datetime_usec, @datetime_leapyear) == {:ok, ~N[2000-02-29 23:50:07]}
+    end
+
+    test "load :utc_datetime_usec" do
+      assert Ecto.Type.load(:utc_datetime_usec, ~N[2015-01-23 23:50:07]) == {:ok, @datetime}
+      assert Ecto.Type.load(:utc_datetime_usec, ~N[2015-01-23 23:50:00]) == {:ok, @datetime_zero}
+      assert Ecto.Type.load(:utc_datetime_usec, ~N[2015-01-23 23:50:07.008000]) == {:ok, @datetime_usec}
+      assert Ecto.Type.load(:utc_datetime_usec, ~N[2000-02-29 23:50:07]) == {:ok, @datetime_leapyear}
+    end
+  end
 end