andrewscaya

SELECT
  event_id,
  date - lag(date) OVER (PARTITION BY event_id ORDER BY date) as difference
FROM
  events;

andrewscaya

SELECT 
  SUM(total) as total,
  SUM(CASE WHEN collected IS TRUE THEN total END) as collected,
  year
 FROM
  invoices
GROUP BY
  year

andrewscaya

CREATE TABLE example (
  id INTEGER PRIMARY KEY NOT NULL,
  data jsonb
);

INSERT INTO example (id, data) VALUES
(1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}'),
(2, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}'),
(3, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}'),
(4, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}'),

andrewscaya

<?php

/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,

andrewscaya

<?php
/**
 * A few ZF2 Zend\Db Examples
 * These examples are based on a gist by Ralph Schindler.
 * (https://gist.github.com/ralphschindler/3949548)
 *
 * @author     Andrew Caya
 * @link       https://github.com/andrewscaya/php_zf2c
 * @license    http://opensource.org/licenses/GPL-2.0
 *             GNU General Public License, version 2 (GPL-2.0)

andrewscaya

<?php

use Zend\Db\Sql\Select;

// basic table
$select0 = new Select;
$select0->from('foo');
// 'SELECT "foo".* FROM "foo"';