🌱 Intervals in Postgres and MySQL

Tables showing arithmetic types and return types
https://www.2ndquadrant.com/en/blog/know-what-time-it-is/

OP	arithmetic	Returns	OP
–	– interval	negative interval	+
	time – interval	time
	interval – interval	interval
	timestamp – timestamp	interval
	date – date	integer (days)
	date – interval	timestamp	*
	time – time	interval
	timestamp – interval	timestamp	/

Examples of using interval values
https://www.educba.com/postgresql-interval/

Example of subtracting timestamps and formatting output string

Postgres

-- Time since last Shipment Label Created
SELECT
  TO_CHAR(CURRENT_TIMESTAMP - (
      SELECT
        created_at FROM cartons
      ORDER BY
        created_at DESC
      LIMIT 1), 'HH24 "hours" MI "min" SS "sec"') AS "Interval";
  created_at from cartons order by created_at desc limit 1;

TIMEDIFF in MySQL
https://www.w3schools.com/SQL/func_mysql_timediff.asp

Formate time in MySQL https://stackoverflow.com/questions/63718977/convert-value-in-timediff-into-string-for-mysql

MySQL

SELECT
  TIME_FORMAT(TIMEDIFF(CURRENT_TIMESTAMP, (
      SELECT
        created_at FROM customers
      ORDER BY
        created_at DESC
      LIMIT 1)), '%H hour %i min %S sec') AS "Interval";