EXTRACT

1
SELECT
2
  EXTRACT(YEAR FROM order_date) as year,
3
  EXTRACT(MONTH FROM order_date) as month,
4
  COUNT(*) as orders
5
FROM orders
6
GROUP BY 1, 2
7
ORDER BY 1, 2;

1
SELECT
2
  EXTRACT(DOW FROM order_date) as day_of_week,
3
  CASE EXTRACT(DOW FROM order_date)
4
    WHEN 0 THEN 'Minggu'
5
    WHEN 1 THEN 'Senin'
6
    WHEN 2 THEN 'Selasa'
7
    WHEN 3 THEN 'Rabu'
8
    WHEN 4 THEN 'Kamis'
9
    WHEN 5 THEN 'Jumat'
10
    WHEN 6 THEN 'Sabtu'
11
  END as day_name,
12
  COUNT(*) as orders
13
FROM orders
14
GROUP BY 1, 2
15
ORDER BY 1;

1
SELECT
2
  EXTRACT(HOUR FROM created_at) as hour,
3
  COUNT(*) as signups
4
FROM users
5
GROUP BY 1
6
ORDER BY 1;

1
SELECT
2
  order_date,
3
  EXTRACT(EPOCH FROM order_date) as unix_timestamp
4
FROM orders
5
LIMIT 5;