How to convert db timestamp to NaiveDateTime

ecto

#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


#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:


Elixir Ecto DateTime Tuple conversion
#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.