![]() ![]() My thought is that I can order by the new UTC column without this performance hit. If I order the existing column by ('Timestamp'::timestamp with time zone) then the query is about 166x slower. New Query (Based on Accepted Solution) EXPLAIN ANALYSE SELECT "Timestamp","Column1","Column2","Column3" FROM time_177168 WHERE "Timestamp">'T00:59:00-06:00' ORDER BY "TimestampUTC" limit 5000 The issue is that ordering on the existing text column results in rows being out of order around DLS changes. This can be a useful way to run an application locally. Sort Method: top-N heapsort Memory: 1089kB Sort Key: ((Timestamp)::timestamp with time zone) > Gather Merge (cost=125337.52 rows=622904 width=81) (actual time=5826.520.5827.743 rows=5000 loops=1) Postgres has a timestamp range type so that is preferred to 2 separate columns for the Postgres implementation. Original Query: EXPLAIN ANALYSE SELECT "Timestamp","Column1","Column2","Column3" FROM time_177168 WHERE "Timestamp">'T00:59:00-06:00' ORDER BY ("Timestamp"::timestamp with time zone) limit 5000 Using Postgres cast operator (::) that becomes: (see demo) select tsasstring::timestamp::time from tablex NOTES: First, heed the comment by FrankHeikens and use the proper data type for your timestamp and not text. Using the solution below I was able to get the query performance to an acceptable level. I am not able to change the data type for the existing "Timestamp" column because of legacy applications that use this database.Īny ideas or suggestion would be greatly appreciated. The overall goal is to be able to quickly order queries by UTC time. Here are a few examples of my attempts to generate the new column but they all return:ĮRROR: generation expression is not immutable SQL state: 42P17Īttempt 1: alter table public.time_177168 ADD COLUMN "TimestampUTC" timestamp with time zone GENERATED ALWAYS AS ("Timestamp"::timestamp with time zone) STOREĪttempt 2: alter table public.time_177168 ADD COLUMN "TimestampUTC" timestamp with time zone GENERATED ALWAYS AS ("Timestamp"::timestamp AT TIME ZONE 'ETC/UTC') STORED ![]() Here is a data example from the table: select "Timestamp",("Timestamp"::timestamp with time zone) from public.time_177168 limit 1 I have tried many methods suggested in forums and documentation but I have not been able to get anything to work. ![]() ![]() I want to generate a timestamptz formatted column with a continuous UTC timestamp but have been unable to do it. MyRecord r = db.insertInto(īut that feels more like a workaround than the right way to do it.My table has a "Timestamp" column (text type) with YYYY-MM-DDTHH:MM:SSZ formatted dates. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |