Insert Ecto schemas in Postgres in the Indian Time Zone (IST - Indian Standard Time)?

Phoenix Forum Questions / Help
, ,
1

Ecto inserts a schema in postgres in the inserted_at and updated_at column without any timezone. How can make these times be in IST Timzone (Indian Standard Time) ?

2

Hello, take a look a https://hexdocs.pm/timex/Timex.html:

Timex is a rich, comprehensive Date/Time library for Elixir projects, with full timezone support via the :tzdata package […]

It lets you do something like Timex.local(timestamp)
using your host’s timezone settings to calculate local time.
Hope it helps.

3

Another way is to use utc, like this

timestamps(type: :utc_datetime)
4

But won’t it store time in UTC timezone? I want it to store in another Time zone.

5

But i dont think local time is what i wish to be inserted because my code is running in a VPS that is not hosted in my country .

6

By default timestamps use NaiveDateTime, and You cannot store time zone.

But You can with utc_datetime.

7

Oh I see, you want to store them with the timezone; my suggestion is not appropriate then. I’m used to store NaiveDateTime and then localize when I need to display them.

8

Ok, if I do the following in the schema

timestamps(type: :utc_datetime)

my timestamps in postgres will be in the Indian Time zone ? and not in UTC time zone ?

Can I do ?

timestamps(type: :ist_datetime)

What are the valid values?

9

I didn’t mean insert local datetime, but insert default NaiveDateTime, then localize when you need to display to users.

If you have SSH access to the VPS you just set the timezone:
I.e. for Ubuntu check how-to-change-time-zone-settings-from-the-command-line

10

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

11

Is there any way I can add 5:30 to the UTC time at the time of insertion ? Then, insertions would happen at my country timezone.

12

This might help https://hexdocs.pm/timex_ecto/Timex.Ecto.html

14

This type does not exists, but can’t You transform utc to ist?

15

I just realised i can do that. Marked your response as solution. Can you guide me though, what would be the best place to do it and how to do it ?

16

That would be when You render…

I have not done that yet, but will have a look later :slight_smile:

17

I think you were getting some confusion from respondents because I believe it is more common to store past times as UTC and to then convert to the desired timezone after grabbing from the database.

I have mostly used the Timex library (https://hexdocs.pm/timex/Timex.html#to_datetime/2) to perform this conversion. For example, to convert a UTC time from the database you can use to_datetime/2…

record = Repo.get(id)
Timex.to_datetime(record.inserted_at, "Asia/Calcutta")

UTC works great for past times and it sounds like that is your only concern here. For future times, you might actually want to store the timezone and “wall” time in the database. I use the Calecto calendar_datetime custom type (https://github.com/lau/calecto#datetime-with-postgres) to store future event times. This should help protect against timezone changes in between the time the event is stored and when it actually takes place.

As an aside, if someone “really” wanted to store past times in a specific time zone, they could probably do so with the Calecto custom type.

2 Likes