1

I am using django models. TIMEZONE in django settings is UTC. and constructing a timestamp by doing some arithmetic.

return queryset.annotate(
            **{f"server_time":RawSQL(
                f'''
                SELECT TO_TIMESTAMP((FLOOR((EXTRACT(EPOCH FROM "{gmdts_table}"."date_key" AT TIME ZONE %s) + %s) / %s)::INTEGER * %s) - %s)::TIMESTAMP
                ''',
                params=[str(requested_time_zone), bucket_offset, time_interval, time_interval, bucket_offset]
            )}
        )

I have timestamp being returned as 2021-07-26 00:00:00 when I am using ::timestamp If I use ::TIMESTAMPTZ, it becomes 2021-07-26 00:00:00+00:00 even though requested_time_zone is 'America/New_York'

I want the output to be 2021-07-26 00:00:00-04:00 ie. show the same time with offset of 'America/New_York appended'. Essentially I just want to append the offset.

I have derived this timestamp by making some calculations and it actually belongs to the timezone 'America/New_York' (it can be any time zone say calculated_time_zone).

How can I update the timezone of this timestamp without actually changing the time for the output, which is:

2021-07-26 00:00:00-04:00

When I use AS TIME ZONE 'America/New_York', I get the output as 2021-07-26 04:00:00+00 which is not what I want.

In python, it can be easily done with a <datetime_object>.replace(tzinfo=new_time_zone). I am looking for the same thing in postgresql.

Any manner in which it can be done with django database functions would be helpful too (I wasn't able to find any)

Improve this question
5
  • This needs a lot more information: 1) What is the data type for the field this is coming from? 2) Where is 2021-07-26 00:00:00+00:00 being returned? 3) Is this being run through a Django model? 4) What are the time zone settings in settings? Add this as update to question. Commented Aug 5, 2021 at 22:50
  • Done! Please answer. Commented Aug 6, 2021 at 23:01
  • What is the data type for "{gmdts_table}"."date_key"? It looks to me that you are making this more complicated then it needs to be. Commented Aug 7, 2021 at 17:05
  • Should have added, in psql client with connection to database what does: show TimeZone; return? Commented Aug 7, 2021 at 17:28
  • TimeZone ---------- Etc/UTC (1 row) Commented Aug 11, 2021 at 22:39

2 Answers 2

Reset to default
2

It's important to understand that the timestamptz type does not store a timezone! It simply stores the UTC timestamp. Which means that your timestamp isn't "in the wrong timezone" because it isn't in any timezone. It is, however, displayed to you in a certain timezone, defined by your session settings.

If you need to shift your timestamp by four hours, as the case may be, then you can add an interval of 4 hours to it. But if you need to "reinterpret" the UTC timestamp to be a timestamp of a different timezone (which means you probably did something wrong when building the timestamp initially), then you can do so by switching to a timezone-naive timestamp and back again:

SELECT timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'

But don't be surprised if this new timestamp is displayed in the same timezone as before, because that's defined by your session settings, not by the timestamp itself.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Strip off the timezone, then add back the new timezone you want. This can be done with:

(myfield::timestamp || '+00:00')::timestamptz
Improve this answer

2 Comments

I dont know what the timezone will be, so I cannot hard code +00:00
If you won't answer the questions I asked in my comment to your question, this is going to be a series of guessing games as to what you have and what you want? Please provide the answers as update to your question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.