Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Code Snippet 1.1 Extracting time from Datetime column
to_timestamp(substring("Datetime", 15, 21), 'HH24:MI:SS')::time
-- Code Snippet 3.1  -  Draft of Pipe Transformation for matches.
SELECT
to_date("Datetime", 'DD Mon yyyy') as EVENT_DATE,
r1."MatchID" as "ID", -- bigint
r1."RoundID" as "RoundID", -- bigint
r1."Home Team Initials" as "HomeTeam", -- bigint
r1."Away Team Initials" as "AwayTeam", -- bigint
null as "VenueID"
FROM
S_WORLDCUPMATCHES r1 -- replace by your table name
WHERE r1."Datetime" is not null and r1."MatchID" is not null
-- Code Snippet 3.2 - World Cup Pipe Transformation.
SELECT
to_date(r1."Year" || '-1-1', 'yyyy-mm-dd') as VALID_FROM,
to_date(r1."Year" || '-12-31', 'yyyy-mm-dd') as VALID_TO,
r1."Year" as "ID", -- bigint
r1."Country" as "Name" -- text
FROM
S_WORLDCUPS r1
-- Code Snippet 3.4  - Players Pipe Transformation.
SELECT
'-infinity'::timestamptz as VALID_FROM,
'infinity'::timestamptz as VALID_TO,
r1."Player Name" as "Name", -- text
r1."MatchID" as "MatchID", -- bigint
r1."Team Initials" as "Team", -- text
r1."Line-up" as "Line-up", -- text
r1."Shirt Number" as "Shirt Number", -- numeric
r1."Position" as "Position", -- text
r1."Event" as "Event" -- text
FROM
S_WORLDCUPPLAYERS r1
-- Code Snippet 3.5 -  Coaches Pipe Transformation.
SELECT
'-infinity'::timestamptz as VALID_FROM
,'infinity'::timestamptz as VALID_TO
,"Team Initials"
,"Coach Name"
,"RoundID"
,"MatchID"
FROM S_WORLDCUPPLAYERS s
-- Code Snippet 3.6 - Round Pipe Transformation.
SELECT DISTINCT
'-infinity'::timestamptz as VALID_FROM,
'infinity'::timestamptz as VALID_TO,
min(to_date(r1."Datetime", 'DD Mon yyyy')) as "Start",
max(to_date(r1."Datetime", 'DD Mon yyyy')) as "End",
r1."RoundID" as "ID", -- bigint
r1."Year" as "WorldCup" -- bigint
FROM
S_WORLDCUPMATCHES r1
WHERE
r1."Year" is not null
AND r1."RoundID" is not null
AND r1."Datetime" != ''
GROUP BY "ID", "WorldCup"
ORDER BY "WorldCup"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment