Skip to content

Instantly share code, notes, and snippets.

@Oloompa
Last active June 28, 2024 11:33
Show Gist options
  • Save Oloompa/d01746c46ae6a111bd1adf006538f64b to your computer and use it in GitHub Desktop.
Save Oloompa/d01746c46ae6a111bd1adf006538f64b to your computer and use it in GitHub Desktop.
syroco
UPDATE vessel
SET "allowedRouteExportFormats" = array_append("allowedRouteExportFormats", 'CMA_GPX')
WHERE id = '389f0460-9df8-41fc-b887-1d241d7cedba';
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;
-- 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;
update vessel
set "allowedRouteExportFormats" = '{WARTSILA_NACOS_PLATINUM_XML,VOYAGE_OVERVIEW_WITH_WEATHER_CSV,SPERRY_MARINE_XML}'
where id = '7a7a009e-d98f-4a94-b6b3-86e0fe424068';
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;
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment