How to convert db timestamp to NaiveDateTime

Questions / Help
1

For some reason modern ecto (2.1.x) is returning timestamps as

{{2017, 6, 17}, {14, 0, 0, 0}}

when select() is used.
e.g.

# schema
schema "table" do
    field :date, :utc_datetime
...

# query
query |> select([t], {t.date}) |> Repo.all
#> {{2017, 6, 17}, {14, 0, 0, 0}}

are there an easy way to convert it to native (naive or not) datetime?
NaiveDateTime.from_erl accepts almost the same format.

iex(101)> NaiveDateTime.from_erl {{2017, 6, 17}, {14, 0, 0, 0}}
** (FunctionClauseError) no function clause matching in NaiveDateTime.from_erl/2
    (elixir) lib/calendar.ex:1214: NaiveDateTime.from_erl({{2017, 6, 17}, {14, 0, 0, 0}}, {0, 0})
iex(101)> NaiveDateTime.from_erl {{2017, 6, 17}, {14, 0, 0}}
{:ok, ~N[2017-06-17 14:00:00]}

also, i can cast to Ecto.DateTime (but there are no easy way to cast from Ecto.DateTime to DateTime)

iex(103)> Ecto.DateTime.cast! {{2017, 6, 17}, {14, 0, 0, 0}}
# #Ecto.DateTime<2017-06-17 14:00:00>

So
 any thoughts?

P.S. real query is using fragment("date_trunc('hour', ?)", t.date) and not just t.date, maybe it’s important

1 Like
2

Using pattern matching + NaiveDateTime.from_erl for now. Not-so-beautiful but works good enough

3

That is why, should use the naive datetime type. :slight_smile:

4

That’s what phoenix (1.3) generator added there. Also, why should i use naive? Don’t see any advantages

5

See at:

https://hexdocs.pm/ecto/Ecto.Schema.html#module-types-and-casting

ECTO TYPE       ELIXIR TYPE
:date           Date
:time           Time
:naive_datetime NaiveDateTime
:utc_datetime   DateTime

You should not be getting a tuple with :utc_datetime anyway.

Basically if you need to know a ‘fixed point in time’ in all reference frames, :naive_datetime is the way to go, it has no timezone information, no oddness.

If you need to some possible relative or timezone-important date (generally anything that may happen in the future) then you want to use :utc_datetime (still should not be getting a tuple).

6

I would like to add that :utc_datetime is certainly the best choice for past events, not necessarily for future events since in the future other timezones you might be trying to address can actually change.

7

just tried :naive_datetime - getting the same thing as a response. underlying database type is timestamp without time zone. so it’s probably something around fragment("date_trunc('hour', ?)", t.date) part, looks like postgres driver and ecto for some reason can’t recognize it.

P.S. Confirmed:

Rate |> limit(1) |> select([e], %{date1: fragment("date_trunc('hour', ?)", e.date), date2: max(e.date)}) |> group_by([e], fragment("date_trunc('hour', ?)", e.date)) |> Repo.all
[%{date1: {{2017, 6, 17}, {2, 0, 0, 0}}, date2: ~N[2017-06-17 02:59:00.000000]}]

P.P.S. Are there any way to hint ecto about proper type?

8

Make sure you use :utc_dateime in both your schemas and migrations. It looks like the cause might be in a migration file, since the type in your DB is timestamp without time zone.

9

just in case: also tried two separate fields, one with timezone, one without. both schemas and migrations.
getting exactly the same results, behavior doesn’t depend on either schema nor underlying database type

10

Er, yes that, timezones for the future, no timezones for the past, is what I meant. ^.^;[quote=“thousandsofthem, post:7, topic:6146”]
P.P.S. Are there any way to hint ecto about proper type?
[/quote]

Ahhh! You are making it forget the type so it does not know how to parse what returns! Look at type/2 to type it again. :slight_smile:

11

Thanks!
Found ecto issues related to type/2 thing:

EDIT:

josevalim commented on Jan 20
The type API is only for casting external parameters. We can likely
generalize it for the use case above.

TL;DR: Don’t work with select().

That’s a bit weird, i thought it’s more or less common usage pattern - applying postgres-awesomeness instead of stupid-simple CRUD :slight_smile:

P.S. Tried it anyway - getting Ecto.Query.CompileError, same as in issue

12

Aww, that sucks
 It really should be usable to cast in select, maybe submit a PR to fix it? :slight_smile:

13

Sorry, my elixir-fu is not that great, tried to look at source code and failed to understand how it works exactly :frowning: good idea though.