Date & Time

Time

TIME is a shorter version 6 digits of the longer 20 digits TIMESTAMP

Timestamp

Syntax: TIMESTAMP(expression, time)
TIMESTAMP which returns a datetime value based on a date or
Timestamp has 20 digits
If the second argument is specified it adds the second argument to the first and returns a datetime
expression is required, if used by itself it returns a date value
time is optional, if used it’ll return a datetime value

SELECT TIMESTAMP("2017-07-23")                  -- returns:2017-07-23 00:00:00 

SELECT TIMESTAMP("2017-07-23",  "13:10:11")    -- returns: 2017-07-23 13:10:11

Current_timestamp

Syntax: CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP returns the current date and time.
Note: The date and time is returned as “YYYY-MM-DD HH-MM-SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric)

SELECT CURRENT_TIMESTAMP() + 1;

Timediff

Syntax: TIMEDIFF(time1, time2)
TIMEDIFF returns the difference between two time expressions
time1 & time2 should be in the same format and both required
calculation is (time1 - time2)

SELECT TIMEDIFF("2017-06-25 13:10:11", "2017-06-15 13:10:10")
-- returns: 240:00:01

Timestampdiff

Timestamp_diff

Syntax: TIMESTAMPDIFF(unit, begin, end)
Syntax: TIMESTAMP_DIFF(begin, end, unit)
TIMESTAMPDIFF() & TIMESTAMP_DIFF() returns the difference between two datetime expressions in years, months, days, hours, minutes, or seconds.
It allows its arguments to have mixed types e.g., begin is a DATE value and end is a DATETIME value. In case you use a DATE value, the TIMESTAMPDIFF function treats it as a DATETIME value whose time part is '00:00:00'
It returns: INT64
TIMESTAMPDIFF only considers the time part that’s relevant to the unit in the argument.
See How To Timestampdiff section for an example
The unit argument determines the unit of the result of (end - begin) represented as an integer. The following are valid units:
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR

Examples

# -- returns the diff between 2010-01-01 and 2010-06-01 in months (you can omit AS)
SELECT   TIMESTAMPDIFF( MONTH, '2010-01-01', '2010-06-01' ) AS result
result: 5

# -- to return the diff in days, change the 1st argument
SELECT   TIMESTAMPDIFF(DAY, '2010-01-01', '2010-06-01') result
result: 151

# -- return the diff between two datetimes in minutes
SELECT   TIMESTAMPDIFF( MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:59') result
result: 45

# -- only considers the time part that's relevant to the unit used in argument 1
# -- example above it ignored the 59 seconds and returned 45 minutes

Timestamp_sub

Syntax: TIMESTAMP_SUB(timestamp_expression, INTERVAL interval_expression date_part)
timestamp_expression: This is the TIMESTAMP value from which you want to subtract.
interval_expression: This is the amount of time you want to subtract from the timestamp.
date_part: This is the unit of the interval expression, such as HOUR, DAY, or MINUTE.
So what’s the difference between TIMESTAMP_SUB & TIMESTAMPDIFF ?
TIMESTAMP_SUB is a function in BigQuery
TIMESTAMP_SUB subtracts a specified amount of time from a given TIMESTAMP value
It is useful when you need to calculate a past date or time from a known point

Example

Imagine you have a TIMESTAMP value representing a specific event, say ‘2024-01-28 10:00:00 UTC’
you want to find out what the time was 3 hours before this event.

SELECT  TIMESTAMP_SUB(TIMESTAMP "2024-01-28 10:00:00 UTC", INTERVAL 3 HOUR) AS ThreeHoursBefore
Returns: TIMESTAMP '2024-01-28 07:00:00 UTC'

Date

Datediff

Returns the number of days between two date values

Syntax: DATEDIFF(date1, date2)
Similar to TIMEDIFF, returns the number of days between two date values
date1 - date2
DATE has 8 digits
It ignores the other time factors, and returns just the number of days

SELECT DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35")
Output: 10

Date_diff

Syntax: DATE_DIFF(date1, date2, date_part)
Similar to TIMESTAMP_DIFF: date1: This is the START DATE value from which you want to subtract.
date2: This is ending date you want to subtract from date1.
date_part: This is the unit of the interval expression, such as HOUR, DAY, or MINUTE can take any of the values in interval you see in the table in DATE_SUB

Date_sub

Syntax: DATE_SUB(date, INTERVAL value interval)
Subtracts a time/date interval from a date and returns the date
Similar to TIMESTAMP_DIFF the interval can be any of the following:

Parameter	Description
date	Required. The date to be modified
value	Required. The value of the time/date interval to subtract. Both positive and negative values are allowed
interval	Required. The type of interval to subtract. Can be one of the following values: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

date

Required. The date to be modified

value

Required. The value of the time/date interval to subtract. Both positive and negative values are allowed

interval

Required. The type of interval to subtract. Can be one of the following values:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

# -- subtract 15 minute from date
SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 15 MINUTE)
Output: 2017-06-15 09:19:21

# -- subtract 3 hour from date
SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 3 HOUR)
Output: 2017-06-15 06:34:21

        
# -- add 2 months to date
SELECT DATE_SUB("2017-06-15", INTERVAL -2 MONTH)
Output: 2017-08-15

Date_add

Similarly we can use _add
If we want to know the day an item day it should be shipped if we have a quality control deadline of 3 days
So if the shipping department wants to know the shipped by date deadline for every order placed (let’s say it’s 3 days from order being placed)

SELECT  DATE_ADD(rescue_date INTERVAL 3 DAY) 
FROM    Petrescue_table

Extract

We can extract portions of a date, time or timestamp using the functions listed above.
It can be used in SELECT clause
It can be used in a WHERE clause

Day

This gives us the calendar day of the month in the date string 2014 - 11 - 22
Output: 22

SELECT  DAY(rescue_date)
FROM    Petrescue_table
WHERE   LCASE(animal_type) = 'cat'

DayofWeek

To extract the day of the week
Output: is whatever M-S is the day

SELECT  DAYOFWEEK(rescue_date)
FROM    Petrescue_table
WHERE   LCASE(animal_type) = 'cat'

Month

SELECT  * 
FROM    Petrescue_table
WHERE   MONTH(rescue_date) = '05'

From_Days

Returns a date from a numeric representation of the day

SELECT  FROM_DAYS(685467):

OUTPUT: 1876-09-29

Current_date

Current_date returns the current date as a string “YYY-MM-DD” or as YYYMMDD (numeric)

Current_time

Just as we used current_timestamp earlier, these two are available as well

SELECT  FROM_DAYS(DATEDIFF(CURRENT_DATE, rescue_date))
FROM    Petrescue

Subtract

We can actually use the simple arithmetic operator to find the difference

SELECT  (CURRENT_DATE - rescue_date)
FROM    Petrescue

Calculate Age

DATEDIFF calculated difference between two dates or timestamps
FROM_DAYS converts a given number of days to YYY-MM-DD format
To calculate the age in YYY-MM-DD format

SELECT  FROM_DAYS(DATEDIFF(CURRENT_DATE, DOB))
FROM    Employees

Summary

This is a summary table of 4 sections, so it’ll be at the end of each of these sections:

Functions
Subqueries
Implicit Joins
DateTime

Command	Syntax (MySQL/DB2)	Description	Example (MySQL/DB2)
COUNT	`SELECT COUNT(column_name) FROM table_name WHERE condition;`	`COUNT` function returns the number of rows that match a specified criterion.	`SELECT COUNT(dep_id) FROM employees;`
AVG	`SELECT AVG(column_name) FROM table_name WHERE condition;`	`AVG` function returns the average value of a numeric column.	`SELECT AVG(salary) FROM employees;`
SUM	`SELECT SUM(column_name) FROM table_name WHERE condition;`	`SUM` function returns the total sum of a numeric column.	`SELECT SUM(salary) FROM employees;`
MIN	`SELECT MIN(column_name) FROM table_name WHERE condition;`	`MIN` function returns the smallest value of the SELECTED column.	`SELECT MIN(salary) FROM employees;`
MAX	`SELECT MAX(column_name) FROM table_name WHERE condition;`	`MAX` function returns the largest value of the SELECTED column.	`SELECT MAX(salary) FROM employees;`
ROUND	`SELECT ROUND(2number, decimals, operation) AS RoundValue;`	`ROUND` function rounds a number to a specified number of decimal places.	`SELECT ROUND(salary) FROM employees;`
LENGTH	`SELECT LENGTH(column_name) FROM table;`	`LENGTH` function returns the length of a string (in bytes).	`SELECT LENGTH(f_name) FROM employees;`
UCASE	`SELECT UCASE(column_name) FROM table;`	`UCASE` function displays the column name in each table in uppercase.	`SELECT UCASE(f_name) FROM employees;`
LCASE	`SELECT LCASE(column_name) FROM table;`	`LCASE` function displays the column name in each table in lowercase.	`SELECT LCASE(f_name) FROM employees;`
DISTINCT	`SELECT DISTINCT column_name FROM table;`	`DISTINCT` function is used to display data without duplicates.	`SELECT DISTINCT UCASE(f_name) FROM employees;`
DAY	`SELECT DAY(column_name) FROM table`	`DAY` function returns the day of the month for a given date.	`SELECT DAY(b_date) FROM employees where emp_id = 'E1002';`
CURRENT_DATE	`SELECT CURRENT_DATE;`	`CURRENT_DATE` is used to display the current date.	`SELECT CURRENT_DATE;`
DATEDIFF()	`SELECT DATEDIFF(date1, date2);`	`DATEDIFF()` is used to calculate the difference between two dates or time stamps. The default value generated is the difference in number of days.	`SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;`
FROM_DAYS()	`SELECT FROM_DAYS(number_of_days);`	`FROM_DAYS()` is used to convert a given number of days to YYYY-MM-DD format.	`SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;`
DATE_ADD()	`SELECT DATE_ADD(date, INTERVAL n type);`	`DATE_ADD()` is used to calculate the date after lapse of mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days after what is mentioned in date column. The type valiable can also be months or years.	`SELECT DATE_ADD(date, INTERVAL 3 DAY);;`
DATE_SUB()	`SELECT DATE_SUB(date, INTERVAL n type);`	`DATE_SUB()` is used to calculate the date prior to the record date by mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days before what is mentioned in date column. The type valiable can also be months or years.	`SELECT DATE_SUB(date, INTERVAL 3 DAY);;`
Subquery	`SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])`	`Subquery` is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.	`SELECT emp_id, f_name, l_name, salaryFROM employeeswhere salary< (SELECT AVG(salary)FROM employees);` `SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all;` `SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs);`
Implicit Inner Join	`SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name;`	`Implicit Inner Join` combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns.	`SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;`
Implicit Cross Join	`SELECT column_name(s) FROM table1, table2;`	`Implicit Cross Join` is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table.	`SELECT * FROM employees, jobs;`