Converting Date-Time column in table to a duration of elapsed time!

Hello!

I’m mid-way through doing the 2nd SQL guided project using R ( Guided Project: Answering Business Questions using SQL.

The project uses the Chinook database, and in the employee table there’s a column called hire_date, which seems to return a date-time format object.

The questions I had in mind were these:

  1. How do you check the data type in an SQL column using SQLite? Most of the responses I saw on StackOverflow for this seemed to only apply to other SQL flavours, either that or I just wasn’t doing it properly.

  2. What’s a relatively simple way to convert the date-time format object into a figure of passed time since the hire date, using SQL queries?? I want to do this so I can make a scatter plot to analyze the relationship between employment length and employee performance!

Thanks in advance for any help on this matter.

Hi @blueberrypudding85,

You can use PRAGMA table_info(table_name) to view the data types of a table.

library(RSQLite)
library(DBI)

db <- 'chinook.db'

run_query <- function(q) {
  conn <- dbConnect(SQLite(), db)
  result <- dbGetQuery(conn, q)
  dbDisconnect(conn)
  return(result)
}

run_query('PRAGMA table_info(employee);')

Output

   cid        name         type notnull dflt_value pk
1    0 employee_id      INTEGER       1         NA  1
2    1   last_name NVARCHAR(20)       1         NA  0
3    2  first_name NVARCHAR(20)       1         NA  0
4    3       title NVARCHAR(30)       0         NA  0
5    4  reports_to      INTEGER       0         NA  0
6    5   birthdate     DATETIME       0         NA  0
7    6   hire_date     DATETIME       0         NA  0
8    7     address NVARCHAR(70)       0         NA  0
9    8        city NVARCHAR(40)       0         NA  0
10   9       state NVARCHAR(40)       0         NA  0
11  10     country NVARCHAR(40)       0         NA  0
12  11 postal_code NVARCHAR(10)       0         NA  0
13  12       phone NVARCHAR(24)       0         NA  0
14  13         fax NVARCHAR(24)       0         NA  0
15  14       email NVARCHAR(60)       0         NA  0

NOTE: SQLite supports the column types used by the majority of SQL implementations however internally it will convert them to one of the following storage types:

  • NULL . The value is a NULL value.
  • INTEGER . The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL . The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT . The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • BLOB . The value is a blob of data, stored exactly as it was input.

You don’t have to worry about it for this case. I just provided an extra information to avoid getting confused when you receive the following output:

run_query('SELECT TYPEOF(hire_date) FROM employee LIMIT 1;')

Output

 TYPEOF(hire_date)
1              text

Internally SQLite stores date as text type.

If you want to calculate the difference between dates then you can do it this way:

run_query("
SELECT
  (strftime('%s','now') - strftime('%s',hire_date)) / 60.0 / 60 / 24
  AS employment_length
FROM
  employee;
")

Output

  employment_length
1         1069.4292
2         1174.4292
3          839.4292
4          807.4292
5          640.4292
6         1005.4292
7          928.4292
8          867.4292

The initial difference was in seconds so I converted it to days. You can convert it into a different value depending on your needs.

I hope this has helped you. If you have any further questions please feel free to let me know. I would be happy to help you.

Best,
Sahil

1 Like

WOW thank you so much for that!

That answered everything I needed and more - my initial plan was to manually key in a certain date but I didn’t realize there was a ‘now’ time string argument!!

2 Likes