Ecto timestamp typecast

Questions / Help
,
1

App has a model Post with fields id, inserted_at. Ecto perfectly works here, very simple. App has 2 different database queries. One query asks for a simple Post. Another query decides it’s not enough inserted_at field and we need another timestamp without timezone taken from another model Queue and added to Post. It looks like this in the most simplified case:

SELECT t1.id, t1.inserted_at, t3.order_timestamp
FROM posts AS t1
JOIN (
	SELECT t2.post_id, t2.inserted_at AS order_timestamp
	FROM queues AS t2
	WHERE t2.post_id = '22'
) as t3 ON t1.id = t3.post_id
ORDER BY t3.order_timestamp DESC

Postgres responses for this two cases are:
1.
id | inserted_at
-----------------------------
1 | 2017-01-24 18:26:36
2 | 2017-01-15 15:55:30

2.
id |     inserted_at             |   order_timestamp
-------------------------------------------------------
1  | 2017-01-24 18:26:36 | 2017-01-25 12:55:11
2  | 2017-01-15 15:55:30 | 2017-01-25 12:55:15

Model Post has a field order_timestamp. But it’s a virtual field. So when Ecto transforms postgres response into Post, it doesn’t use order_timestamp virtual field and can return database response for this column as erl date separately from Post. I have tried to remove virtual but Ecto doesn’t accept this idea too.
Any suggestions how I can inject order_timestamp into the model?

Summarizing this in one sentence - is it possible to encode postgres response with variable number of columns into model just by registering optional column names as fields(or any other structure)

UPDATE:

I have found it works even without virtual attributes by:
select: %{request: r, order_timestamp: qf.order_timestamp},
but Ecto(Postgrex?) doesn’t want to convert from erl to naive datetime. I extracted unix timestamp in postgres and now order_timestamp is no more naive datetime but simply integer and no more added to a model but separate object in response. It works for me but I don’t really like it.

1 Like
2

Are you opposed to creating a model for your query?

def MyApp.Query do

  use Ecto.Schema

  embedded_schema do
    field :id, :integer
    field :inserted_at, :naive_datetime
    field :order_timestamp, :naive_datetime
  end
end
1 Like
3

Not sure I know how to use embedded schema in this case. I can’t use embeds_one for Post to insert MyApp.Query because Post will nest the same fields it already has. And I can’t use “from q in MyApp.Query”, Ecto doesn’t like this, just tried.

I have tried also :

schema "posts" do
   embeds_one :order_timestamp, :naive_datetime
   timestamps()
end

but Ecto errors that order_timestamp should belong to t1.

1 Like
4

If this is a view you’ll be accessing repeatedly, you can add it to your migrations and create a schema for it. Then you’ll get the right data types when you returns data from it.

Check out this article on the subject.

I feel like I’ve encountered what you’re seeing before but just can’t remember how I solved it :confused:

1 Like
5

Thanks. But views will not work for me. Actually that date is in schema already and I can just join that entity. But I’m trying to make it in a dynamic way)) There is another option to build json in postgres.

1 Like