ebean version column postgresql timestamp

In addition, the usual comparison operators shown in Table 9.1 are available for the date/time types. You should be familiar with the background information on date/time data types from Section 8.5. For formatting functions, refer to Section 9.8. Table 9.32 illustrates the behaviors of the basic arithmetic operators ( +, *, etc.). Table 9.33 shows the available functions for date/time value processing, with details appearing in the following subsections. 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

Sort Method: top-N heapsort Memory: 1089kB Sort Key: ((Timestamp)::timestamp with time zone) 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 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. My table has a 'Timestamp' column (text type) with YYYY-MM-DDTHH:MM:SSZ formatted dates.

Ebean version column postgresql timestamp