Skip to content

5. Date.md

Latest commit

 

History

History
143 lines (111 loc) · 6.04 KB

5. Date.md

File metadata and controls

143 lines (111 loc) · 6.04 KB

Intro

Dates/Times in SQL are a complex topic, deserving of a category of their own. They're also fantastically powerful, making it easier to work with variable-length concepts like 'months' than many programming languages. Before getting started on this category, it's probably worth taking a look over the PostgreSQL docs page on date/time functions. You might also want to complete the aggregate functions category, since we'll use some of those capabilities in this section.

Q1: produce a timestamp for 1 a.m. on the 31st of aug 2012

Produce a timestamp for 1 a.m. on the 31st of August 2012.

-- select cast('2012-08-31 01:00' as timestamp);
-- select '2012-08-31 01:00'::timestamp;
-- select to_timestamp('2012 Aug 31 01:00', 'YYYY Mon DD HH24:M1');
select timestamp '2012-08-31 01:00';
select timesstamp with time zone '2012-08-31 01:00:00  +00:00';

Q2: substract timestamp from each other

Find the result of subtracting the timestamp '2012-07-30 01:00:00' from the timestamp '2012-08-31 01:00:00'

select '2012-08-31 01:00:00'::timestamp - '2012-07-30 01:00:00'::timestamp as interval;

解析

时间日期类型均支持加减乘除操作

Q3:

generate a list of all the dates in october 2012

select * from generate_series('2012-10-01'::timestamp, '2012-10-31', '1 day')

解析 generate_series的语法如下:

Function Argument Type Return Type Description
generate_series(start, stop) int or bigint setof int or setof bigint (same as argument type) Generate a series of values, from start to stop with a step size of one
generate_series(start, stop, step) int or bigint setof int or setof bigint (same as argument type) Generate a series of values, from start to stop with a step size of step
generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step

Q4: get the day of month from a timestamp

Get the day of the month from the timestamp '2012-08-31' as an integer.

select extract(day from '2012-03-31'::timestamp)

解析

extract函数语法如下: extract(field from source) field值可以为century, year, month, day, hour, minute, second, week(当前日期属于年份的第几周), doy(当前日期属于年份的第几天)

Q5: workout the number of seconds between timestamps

Work out the number of seconds between the timestamps '2012-08-31 01:00:00' and '2012-09-02 00:00:00'

-- 使用extract(epoch from source)
select extract(epoch from timestamp '2012-09-02 00:00:00' - timestamp '2012-08-31 01:00:00')

-- 使用暴力方法
with t as (select timestamp '2012-09-02 00:00:00' - timestamp '2012-08-31 01:00:00' as intv)
select extract(day from intv)*24*60*60 +
	extract(hour from intv)*60*60 +
	extract(minute from intv)*60 +
	extract(second from intv)
	from t

解析

extract(epoch from source)中的source为一个时间间隔时,是将时间间隔转换为秒, 当时一个时间时是计算距离1970-01-01 00:00:00-00多少秒

Q6: workout the number of days in each month of 2012

For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type.

with ymonths as (select generate_series(timestamp '2012-01-01', '2012-12-01', '1 month') as ymonth)
select extract(month from ymonth) as month,
	ymonth + interval '1 month' - ymonth as lengh
	from ymonths;

Q7: workout the number of days remaining in the month

For any given timestamp, work out the number of days remaining in the month. The current day should count as a whole day, regardless of the time. Use '2012-02-11 01:00:00' as an example timestamp for the purposes of making the answer. Format the output as a single interval value.

select timestamp '2012-02-01' + interval '1 month' -  timestamp '2012-02-11' as remaining;

Q8: workout the end time of bookings

Return a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system. image

-- 答案是错的,它用limit筛选前十个是不对的,应该用rank函数筛选前十个
with bookings as (select starttime, slots, rank() over(order by starttime desc)
				  	from cd.bookings
				 order by starttime)
select starttime, (starttime + (interval '0.5 hour'*slots)) as endtime, rank
	from bookings
	where rank between 1 and 10
order by endtime desc;

Q9: return a count of bookings for each month

Return a count of bookings for each month, sorted by month image

select date_trunc('month', starttime) as month, count(*)
	from cd.bookings
	group by month
	order by month;

解析

date_trunc函数用于设置时间的精度, 通用公式为date_trunc(text, timestamp)

Q10: work out the utilisation percentage for each facility by month

Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open. image

select name, month, 
	round((100*slots)/
		cast(
			25*(cast((month + interval '1 month') as date)
			- cast (month as date)) as numeric),1) as utilisation
	from  (
		select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
			from cd.bookings bks
			inner join cd.facilities facs
				on bks.facid = facs.facid
			group by facs.facid, month
	) as inn
order by name, month