Convert date, coming from a select in the database

Questions / Help
1

I have a query that returns me a select from the data bank.
I need to cast the column (:time_end), in UTC-3

How to convert?

Below my function.

Function:

    def all() do
        query = from(a in Table, [
            limit: 500,
            order_by: [desc: a.time_start],
          ])
        Repo.all(query)
    end

Result:

[
  %Table{
    __meta__: #Ecto.Schema.Metadata<:loaded, "test">,
    name: "Example 1",
    column_1: "Example 1,
    column_2: nil,
    time_end: ~U[2019-10-04 16:27:00Z],
    time_start: ~U[2019-10-12 19:16:00Z],
  }
]
2

You can ask the database to calculate the time for you. Something like this (not tested):

    def all() do
       query = from(a in Table, [
         select: [
           fragment("a.time_start - interval '3 hours' as time_start"),
           ...
          ],
          limit: 500,
          order_by: [desc: a.time_start],
        ])
        Repo.all(query)
    end
1 Like
3

The general best practice is to handle UTC and only convert timezones as close to the output of the system as possible. E.g. in the view layer for websites/apis or even in the browser.

2 Likes
4

Oh yes, but would there be any way to convert, without the need to mess with the select?

How do I convert the date (~U[2019-10-12 19:16:00Z]) to UTC-3?

I wanted to use something like
DateTime.new

5

this will help me.
Timex.shift(date, hours: -3)
Thank you for your help.

6

This will shift the time within UTC. If you want to change the TZ of given DateTime then you can use DateTime.shift_zone(date, requested_tz).

4 Likes