How to calculate if table rows are inserted consecutively in MySQL

As a developer, working with asynchronous code often means dealing with out-of-order timestamps. To report on such data, we need to validate the created date-time. Here, we will explore SQL parts beyond the SELECT statement, focusing on variables and the HAVING clause.

SELECT @a := 0;
SELECT invited, `date`, `date` - @a AS gap, @a := `date`
FROM INVOICES AS i
HAVING gap < 0

SQL Variables

SQL variables are used to store temporary data. They are particularly useful in complex queries where intermediate results need to be stored and reused.

SELECT @a := 0;

• @a is a user-defined variable.
• := is the assignment operator.
• 0 is the initial value assigned to @a.

Using Variables in Queries

Variables can be used within queries to perform calculations or store intermediate results.

SELECT invited, `date`, `date` - @a AS gap, @a := `date`
FROM INVOICES AS i
HAVING gap < 0;

• invited and date are columns from the INVOICES table.
• date - @a AS gap calculates the difference between the current date and the value stored in @a, aliasing it as gap.
• @a := date updates the variable @a with the current date value for the next row.

The HAVING Clause

The HAVING clause is used to filter results based on aggregate functions or calculated columns, similar to the WHERE clause but applied after the GROUP BY clause.

HAVING gap < 0;

• This filters the results to include only rows where the gap is less than 0, indicating an out-of-order timestamp.

Full Query Explanation

1. Initialize Variable:

   SELECT @a := 0;

   Initializes the variable @a to 0.
2. Query with Variable:

   SELECT invited, `date`, `date` - @a AS gap, @a := `date`
   FROM INVOICES AS i
   HAVING gap < 0;

   • Selects invited and date from the INVOICES table.
   • Calculates the gap between the current date and the previous date stored in @a.
   • Updates @a with the current date.
   • Filters rows where gap is less than 0.

Linux Clipboard Text To Speech

Install festival and esound-clients.

To do so, follow this tutorial: https://help.ubuntu.com/community/TextToSpeech

Install xsel:

sudo apt-get install xsel

Create new file in your home folder, call it "clipboardtospeech.sh" or something.

nano ~/apps/clipboardtospeech.sh

edit it and paste the following line:

xsel --clipboard | festival --tts

Make the script executable

cmod +x ~/apps/clipboardtospeech.sh

Make the file executable make a hotkey that launches the shell script:

~/apps/clipboardtospeech.sh 

Rsync over proxy gcloud / jumpbox

So I needed to pushdown some log files off of a box without a public IP, rather then rsync to the jump box / proxy then  rsync to my local box i made use of the ssh proxy command.

 rsync -avz -e 'ssh -o ProxyCommand="ssh proxxye.url.com -i ~/.ssh/google_compute_engine -W my-internal-ip.internal:22"' my-internal-ip.internal:/var/www/log/workers ~/code/logs/