psql (9.6.7, server 11.3) on linux
In the copy/paste below, first 2 lines returned by a select on the view, why didn't it sort on start_datetime correctly ? I would think that the one started on 04-08 would come before the one on 04-09 ?
dvdb=> \d sim_phases;
Table "dvm.sim_phases"
Column | Type | Modifiers
----------------+--------------------------+-----------
sj_id | integer |
sim_phase_name | character varying |
status | character varying |
error_message | character varying |
start_datetime | timestamp with time zone |
end_datetime | timestamp with time zone |
duration_hrs | numeric(5,2) |
Check constraints:
"sim_phases_name_check" CHECK (sim_phase_name::text = ANY (ARRAY['presim'::character varying::text, 'runsim'::character varying::text, 'postsim'::character varying::text, NULL::character varying::text]))
"sim_phases_status_check" CHECK (status::text = ANY (ARRAY['in_progress'::character varying, 'completed'::character varying, 'passed'::character varying, 'failed'::character varying, NULL::character varying]::text[]))
Foreign-key constraints:
"sim_phases_sj_id_fkey" FOREIGN KEY (sj_id) REFERENCES sim_jobs(sj_id) ON DELETE CASCADE
dvdb=> create or replace view spview as (select sj_id,sim_phase_name,status,start_datetime,end_datetime,duration_hrs from dvm.sim_phases order by sj_id,start_datetime);
CREATE VIEW
dvdb=> select * from spview where sj_id in (select sj_id from sjview where dvm_id = 1102);
sj_id | sim_phase_name | status | start_datetime | end_datetime | duration_hrs
-------+----------------+-------------+------------------------+------------------------+--------------
6269 | runsim | in_progress | 2020-04-09 03:39:13-04 | |
6269 | presim | completed | 2020-04-08 23:11:21-04 | 2020-04-09 03:39:13-04 | 4.46
6267 | runsim | in_progress | 2020-04-09 02:21:38-04 | |
6267 | presim | completed | 2020-04-08 23:11:21-04 | 2020-04-09 02:21:38-04 | 3.17
