Skip to content

Instantly share code, notes, and snippets.

@rturowicz
Last active December 17, 2015 14:29
Show Gist options
  • Save rturowicz/5624570 to your computer and use it in GitHub Desktop.
Save rturowicz/5624570 to your computer and use it in GitHub Desktop.
postgresql: mysql functions
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITH TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
CREATE OR REPLACE FUNCTION FROM_UNIXTIME(BIGINT)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * interval \'1 second\';';
-- GROUP_CONCAT:
-- data
-- id val
-- ----- ------
-- 1 a
-- 2 b
-- 2 c
-- expected output
-- id cnt tmp
-- ----- ------ ------------------
-- 1 1 a
-- 2 2 b, c
-- 1
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
SELECT id, count(*) AS "cnt", array_to_string(array_accum(val), ', ') FROM example
GROUP BY id
ORDER BY id;
-- 2
SELECT t.id, count(t.*) AS "cnt", array_to_string(ARRAY(SELECT val FROM example WHERE id = t.id ORDER BY val), ', ')
FROM example AS t
GROUP BY t.id
ORDER BY t.id;
-- TIMESTAMP:
CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment