The Query (aka Kyle and Cody) here 👋 

Here’s what we have for you today:

• A new slate of entry-level, remote jobs!

• Kelly Adams teaches us date_add and date_sub

• An interesting lesson on working with timestamps

• A meme that I know you will relate to 🤣

Looking to become a Data Analyst? START HERE.

select * from data-jobs

remote, entry-level data jobs

Because who likes writing SQL from a busy office?

1. Data Analyst, Budget @ General Dynamics — $66k-99k (apply here)

2. Strategy & Analytics Associate @ Seer Interactive — $65k-73k (apply here)

3. Data Analyst @ Crowdstrike — $65k-95k (apply here)

freelance gigs

Need work experience? Get real experience with real projects.

1. Excel Dashboard Redesign — $65 per hour (apply here)

2. Looker Studio Dashboard — $80 fixed price (apply here)

3. Python Data Analyst Project— $ 30 per hour (apply here)

def creator_content_spotlight(🔦):

Cody here 👋 — if you don’t follow Kelly Adams on LinkedIn, you’re missing out.

She consistently puts out great content and today was no different.

Her post is about DATE_ADD and DATE_SUB, two SQL functions I use frequently.

One example is in the WHERE clause when I want dynamic date logic like, “last 7 days from yesterday.”

It’d look like this:

select
    *
from table

where date_column between date_sub(current_date() - 1, interval 7 day) and current_date() - 1

Give Kelly a follow and check out her post 👇️ 

class SQLMiniLesson:

Working with UNIX Timestamps in SQL

Kyle here 👋 — Have you ever seen a timestamp that just looked like a really long number?

Timestamps usually look something like this: "2021-06-14 12:33:07"

But sometimes they can look like this: “1623647987” 🤔

I see this when I’m working with data coming from APIs.

It’s called a UNIX Timestamp.

It’s a representation of time as the number of seconds that have elapsed since January 1, 1970, 00:00:00 Coordinated Universal Time (UTC), not counting leap seconds.

This point in time is called the "UNIX Epoch."

UNIX timestamps are a convenient way to store and manipulate date and time values in various programming languages and databases, including SQL.

They are especially useful for calculating time intervals and comparing dates since they represent time as a single numeric value.

Here’s how to work with UNIX Timestamps in SQL:

Different SQL databases have their own set of functions to handle UNIX timestamps.

Here are some examples showing how PostgreSQL, MySQL, and BigQuery handle these.

Convert a UNIX timestamp to a date and time value using the TIMESTAMP_SECONDS() function:

SELECT TIMESTAMP_SECONDS(1623647987) AS date_time;

Convert a date and time value to a UNIX timestamp using the UNIX_SECONDS() function:

SELECT UNIX_SECONDS(TIMESTAMP "2021-06-14 12:33:07") AS unix_timestamp;

Let's consider an example where we have a table named user_logins with the following data:

We want to convert the UNIX timestamps in the login_unix_timestamp column to human-readable date and time values. In PostgreSQL, we can do this using the to_timestamp() function:

Next time you see a timestamp shown as a long string of numbers you will know what to do!

import memes as 😂 

Lol this is so true, especially when switching between Excel and Google Sheets (for some reason they’re reversed in Sheets! 😡)

content & resources 🤓 

1. Become a Data Analyst Guide: Our full guide on what it takes to land a job as a data analyst.

2. Download our SQL Cheatsheet as a PDF and desktop wallpaper here. 

3. LinkedIn: We create content on LinkedIn daily. You can follow Cody here and Kyle here.

4. Courses: Our course on showcasing your data portfolio is live!

That’s it for today.

Stay crunchin’ folks and see you next week!

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -