syroco

add-export-format-to-vessel.sql

UPDATE vessel
SET "allowedRouteExportFormats" = array_append("allowedRouteExportFormats", 'CMA_GPX')
WHERE id = '389f0460-9df8-41fc-b887-1d241d7cedba';

first-accepted-route-proposal.sql

select * from voyagerouteproposal v
where v."optimizationId" in (
  select "id" from optimization o
  where o."voyageId" = '060b6dd5-7f54-4cff-97c5-01e7de5a361e'
)
and v."completionReason" = 'ACCEPTED'
order by v."createdAt" ASC
limit 1;

optimization time debugging.sql

-- list last optimization of a vessel
select o.id, o."createdAt", o."completedAt", o."completionReason" from optimization o
join voyage v on o."voyageId" = v.id
where v."vesselId"='e4e495f4-e5c7-4abe-9bd3-0645a4e54fef'
order by o."createdAt" desc
limit 5;

-- count all optimization in a given period
select count(distinct l."optimizationId") from optimizationstatuslog l
where l."createdAt" > '2024-04-15 12:07:34.333'
and l."createdAt" < '2024-04-15 12:10:47.026';

-- voyage duration statistics
SELECT 
    AVG(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS moyenne_duree,
    MIN(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS min_duree,
    MAX(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS max_duree,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS mediane_duree
FROM voyage v
WHERE dta IS NOT NULL;

-- get the longest voyage
SELECT id, EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400 AS duree
FROM voyage v
WHERE dta IS NOT NULL
ORDER BY duree DESC
LIMIT 1;

set-allowed-route-export-formats.sql

update vessel
set "allowedRouteExportFormats" = '{WARTSILA_NACOS_PLATINUM_XML,VOYAGE_OVERVIEW_WITH_WEATHER_CSV,SPERRY_MARINE_XML}'
where id = '7a7a009e-d98f-4a94-b6b3-86e0fe424068';

sync-completion-reasons-of-voyages.sql

update voyage v
set "completionReason" = (
  select l."newCompletionReason"
  from voyagestatuslog l
  where l."voyageId" = v.id
  order by l."createdAt" desc
  limit 1
)
where "completionReason" is Null;

vessel-locations-between-dates.sql

-- all vessel locations between two dates
select l."recordedAt", l."speedOverGround", l.latitude, l.longitude
from vessellocation l
where l."vesselId" = 'f049040c-56ca-4f6a-8d59-aba0f6399761'
and l."recordedAt" > '2024-02-12'
and l."recordedAt" <'2024-02-22'
order by l."recordedAt" desc;

-- vessel locations at port between two dates
select l."recordedAt", l."speedOverGround", l.latitude, l.longitude
from vessellocation l
where l."vesselId" = '2a12be9f-424f-40fb-b0dd-e4d8b1acf388'
and l."recordedAt" > '2024-02-01'
and l."recordedAt" <'2024-02-04'
and l."speedOverGround" < 1
order by l."recordedAt" desc;