marcomalva

WITH tz AS (
  SELECT gc.value AS tzv FROM public.gv_config gc WHERE gc.name =  'arena.timezone' LIMIT 1
)
SELECT 
        id
        , (NOW() AT TIME ZONE tz.tzv) now_arena
        , (NOW() AT TIME ZONE tz.tzv)::date now_arena_date
FROM 
        public.event_details ed
        , tz tz

marcomalva

# Source: https://bytefreaks.net/applications/gnuplot/gnuplot-create-a-plot-with-date-time-on-x-axis
# Multiplot/pane: http://www.gnuplotting.org/tag/multiplot/
#
# Setting output to be a PNG file of size 'width'x'height'
#  'width' and 'height' are set from the command line:
#
#   gnuplot -e "filename='server_1.csv'; width=10000; height=500;" timeDifference.plot
#
#Setting the font of all text to be 'Verdana' size 8
set terminal pngcairo size width,height enhanced font 'Verdana,8'

marcomalva

# Source: http://www.gnuplotting.org/tag/multiplot/
### Start multiplot (2x2 layout)
set multiplot layout 2,2 rowsfirst
# --- GRAPH a
set label 1 'a' at graph 0.92,0.9 font ',8'
plot f(x) with lines ls 1
# --- GRAPH b
set label 1 'b' at graph 0.92,0.9 font ',8'
plot g(x) with lines ls 1
# --- GRAPH c

marcomalva

# self describing makefile
#    based on idea in https://gist.github.com/jeffsp/3e1b2e10b9181681a604dd9ec6d64ecf
#
# add support for any Makefile name, see https://www.gnu.org/software/make/manual/make.html#Special-Variables
# add colon after target name so bat -l mk gives nicer display on terminal
# for nicer Markdown add asterix around \1 in sed, e.g. for bat or mdcat
#
# Usage
# ==========
#

marcomalva

# Makefile to build ...
#
# for help run: make help
# nicer output with: make help | bat -l mk
#
# an alternative for build-in help w/ color coding and w/o .PHONY:
# https://github.com/Byron/dua-cli/blob/d0e85fec1586a8937928472e361837ef21e40b14/Makefile
#
BINARY=$(shell basename $(shell pwd))
VERSION=$(shell git describe --tags --dirty)

marcomalva

-- https://github.com/dalibo/pev2
--
-- Save the query as an SQL file and paste the JSON file into the index.html
--
-- Download the index.html page with the command below and open it in your web browser:
--    wget https://www.github.com/dalibo/pev2/releases/latest/download/index.html
--
-- Obtain the database query plan with command below:
--    psql -h ${DB_HOST:-localhost} -p ${DB_PORT:-5432} -U ${DB_USER:-db_user} -XqAt -f "${filename}" > "analyze-${filename%.*}.json"
--

marcomalva

-- last element in a JSON array, https://www.appsloveworld.com/postgresql/100/89/find-last-element-of-array-in-json-column-type
select '[[1479772800000, 70.12], [1479859200000, 70.83], [1480032000000, 71.23]]'::json->-1;

-- check if JSON array contains a string, https://stackoverflow.com/a/27144175
create table rabbits (rabbit_id bigserial primary key, info json not null);
insert into rabbits (info) values
  ('{"name":"Henry", "food":["lettuce","carrots"]}'),
  ('{"name":"Herald","food":["carrots","zucchini"]}'),
  ('{"name":"Helen", "food":["lettuce","cheese"]}');
  

marcomalva

-- check if array contains a value or multiple values
-- answer copied from https://stackoverflow.com/a/54069718

-- search for single value in array
SELECT * FROM mytable WHERE 'Book' = ANY(pub_types);

-- search an array that contains multiple values **together**
SELECT * FROM mytable WHERE pub_types @> '{"Journal", "Book"}';

--search an array that contains **one of** some values.

marcomalva

-- show tables/view dependent on object source_schema_name.source_table_name
-- change WHERE clause to change object
SELECT distinct current_database() 
, dependent_ns.nspname as dependent_schema
, dependent_view.relname as dependent_view
, source_ns.nspname as source_schema
, source_table.relname as source_table
-- , pg_attribute.attname as column_name
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid