التعامل مع الطوابع الزمنية

تُعد التواريخ والأوقات في SQL موضوعًا معقدًا يستحق فئة خاصة به. وهي أيضًا قوية بشكل مذهل، وتجعل التعامل مع مفاهيم متغيرة الطول مثل "الأشهر" أسهل من كثير من لغات البرمجة.

وقبل البدء في هذه الفئة، يجدر على الأرجح إلقاء نظرة على صفحة الوثائق الخاصة بـ PostgreSQL عن دوال التاريخ والوقت. وقد تريد أيضًا إكمال فئة الدوال التجميعية (aggregate functions)، لأننا سنستخدم بعض تلك الإمكانات في هذا القسم.

1. إنتاج طابع زمني (timestamp) للساعة 1 صباحًا في 31 أغسطس 2012

السؤال

أنتج طابعًا زمنيًا للساعة 1 صباحًا في 31 أغسطس 2012.

النتائج المتوقعة

timestamp
2012-08-31 01:00:00

الإجابة

select timestamp '2012-08-31 01:00:00';

هذا سؤال سهل إلى حد كبير لنبدأ به! فلـ SQL مجموعة من أنواع التاريخ والوقت المختلفة، يمكنك تصفّحها على راحتك في وثائق Postgres الممتازة. وتتيح لك هذه الأنواع ببساطة تخزين تواريخ أو أوقات أو طوابع زمنية (تاريخ + وقت).

الإجابة المعتمدة هي أفضل طريقة لإنشاء طابع زمني في الظروف العادية. ويمكنك أيضًا استخدام التحويلات (casts) لتغيير نص منسّق بشكل صحيح إلى طابع زمني، على سبيل المثال:

select '2012-08-31 01:00:00'::timestamp;
select cast('2012-08-31 01:00:00' as timestamp);

المقاربة الأولى امتداد خاص بـ Postgres، أما الثانية فهي معيار SQL. وستلاحظ أننا استخدمنا في كثير من أسئلتنا السابقة نصوصًا مجرّدة دون تحديد نوع بيانات. وهذا يعمل لأن Postgres، حين يتعامل مع قيمة صادرة من عمود طابع زمني في جدول (مثلًا)، يعرف أنه يجب تحويل نصوصنا إلى طوابع زمنية.

يمكن تخزين الطوابع الزمنية مع معلومات المنطقة الزمنية أو دونها. وقد اخترنا عدم ذلك هنا، لكن إن أردت يمكنك تنسيق الطابع الزمني مثل "2012-08-31 01:00:00 +00:00"، بافتراض التوقيت العالمي المنسّق (UTC). ولاحظ أن الطابع الزمني مع المنطقة الزمنية نوع مختلف عن الطابع الزمني — فعند التصريح عنه، ينبغي استخدام TIMESTAMP WITH TIME ZONE 2012-08-31 01:00:00 +00:00.

وأخيرًا، جرّب قليلًا بعض الصيغ المختلفة لتسلسل التاريخ والوقت الواردة في وثائق Postgres. وستجد أن Postgres مرن للغاية في الصيغ التي يقبلها، وإن كانت وصيتي لك استخدام التسلسل المعياري الذي استخدمناه هنا — ستجده واضحًا لا لبس فيه وسهل النقل إلى قواعد بيانات أخرى.

تلميح: هناك طرق كثيرة لفعل ذلك، لكن الأسهل على الأرجح هو النظر إلى الكلمة المفتاحية TIMESTAMP.

2. طرح الطوابع الزمنية من بعضها

السؤال

أوجد نتيجة طرح الطابع الزمني '2012-07-30 01:00:00' من الطابع الزمني '2012-08-31 01:00:00'

النتائج المتوقعة

interval
32 days

الإجابة

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

ينتج عن طرح الطوابع الزمنية نوع بيانات INTERVAL. والفترات (INTERVALs) نوع بيانات خاص لتمثيل الفرق بين نوعَي TIMESTAMP. وعند طرح الطوابع الزمنية، يعطي Postgres عادةً فترة بالأيام والساعات والدقائق والثواني، دون الخوض في الأشهر. وهذا يسهّل الحياة عمومًا، لأن الأشهر متغيرة الأطوال.

ومن الأمور المفيدة في الفترات أنها تستطيع ترميز الأشهر. ولنتخيل أنني أريد جدولة شيء ليحدث بعد شهر واحد بالضبط، أيًا كان طول الشهر. لفعل ذلك، يمكنني استخدام [timestamp] + interval '1 month'.

وتقف الفترات على النقيض من معالجة SQL لأنواع DATE. فالتواريخ لا تستخدم الفترات — بل يُعيد طرح تاريخين عددًا صحيحًا يمثّل عدد الأيام بين التاريخين. ويمكنك أيضًا إضافة قيم صحيحة إلى التواريخ. وهذا أكثر ملاءمة أحيانًا، بحسب قدر الذكاء الذي تحتاجه في التعامل مع تواريخك!

تلميح: يمكنك استخدام الرمز '-' على الطوابع الزمنية

3. توليد قائمة بكل التواريخ في أكتوبر 2012

السؤال

أنتج قائمة بكل التواريخ في أكتوبر 2012. ويمكن إخراجها كطابع زمني (بوقت منتصف الليل) أو كتاريخ.

النتائج المتوقعة

ts
2012-10-01 00:00:00
2012-10-02 00:00:00
2012-10-03 00:00:00
2012-10-04 00:00:00
2012-10-05 00:00:00
2012-10-06 00:00:00
2012-10-07 00:00:00
2012-10-08 00:00:00
2012-10-09 00:00:00
2012-10-10 00:00:00
2012-10-11 00:00:00
2012-10-12 00:00:00
2012-10-13 00:00:00
2012-10-14 00:00:00
2012-10-15 00:00:00
2012-10-16 00:00:00
2012-10-17 00:00:00
2012-10-18 00:00:00
2012-10-19 00:00:00
2012-10-20 00:00:00
2012-10-21 00:00:00
2012-10-22 00:00:00
2012-10-23 00:00:00
2012-10-24 00:00:00
2012-10-25 00:00:00
2012-10-26 00:00:00
2012-10-27 00:00:00
2012-10-28 00:00:00
2012-10-29 00:00:00
2012-10-30 00:00:00
2012-10-31 00:00:00

الإجابة

select generate_series(timestamp '2012-10-01', timestamp '2012-10-31', interval '1 day') as ts;

من أفضل ميزات Postgres على قواعد البيانات الأخرى دالة بسيطة تُسمى GENERATE_SERIES. وتتيح لك هذه الدالة توليد قائمة تواريخ أو أرقام، مع تحديد بداية ونهاية وقيمة زيادة. وهي مفيدة للغاية في الحالات التي تريد فيها إخراج، مثلًا، المبيعات اليومية على مدار شهر. والطريقة المعتادة لفعل ذلك على جدول يحتوي قائمة مبيعات قد تكون استخدام تجميع SUM، مع التجميع بحسب التاريخ ونوع المنتج. ولسوء الحظ، في هذه المقاربة خلل: إذا لم تكن هناك مبيعات في يوم معيّن، فلن يظهر! ولجعلها تعمل على نحو صحيح، تحتاج إلى ربط خارجي من قائمة متسلسلة من الطوابع الزمنية إلى البيانات المجمّعة لملء الفراغات.

وفي أنظمة قواعد بيانات أخرى، ليس غريبًا الاحتفاظ بـ"جدول تقويم" مليء بالتواريخ تنفّذ عليه عمليات الربط هذه. أو يمكنك في بعض الأنظمة كتابة نظير لـ generate_series باستخدام تعبيرات CTE التعاودية. ولحسن حظنا، يسهّل Postgres حياتنا كثيرًا!

تلميح: ألقِ نظرة على دالة GENERATE_SERIES في Postgres

4. الحصول على يوم الشهر من طابع زمني

السؤال

الحصول على يوم الشهر من الطابع الزمني '2012-08-31' كعدد صحيح.

النتائج المتوقعة

date_part
31

الإجابة

select extract(day from timestamp '2012-08-31');

تُستخدم دالة EXTRACT للحصول على أجزاء من طابع زمني أو فترة. ويمكنك الحصول على قيمة أي حقل في الطابع الزمني كعدد صحيح.

تلميح: أسهل طريقة لفعل ذلك هي دالة EXTRACT.

5. حساب عدد الثواني بين طابعين زمنيين

السؤال

احسب عدد الثواني بين الطابعين الزمنيين '2012-08-31 01:00:00' و'2012-09-02 00:00:00'

النتائج المتوقعة

date_part
169200

الإجابة

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

الإجابة أعلاه خدعة خاصة بـ Postgres. فاستخراج الـ epoch يحوّل فترة أو طابعًا زمنيًا إلى عدد من الثواني، أو إلى عدد الثواني منذ الـ epoch (1 يناير 1970) على التوالي. وإن أردت عدد الدقائق أو الساعات أو نحو ذلك، يمكنك ببساطة قسمة عدد الثواني على نحو مناسب.

وإن أردت كتابة شيفرة أكثر قابلية للنقل، فستجد للأسف أنك لا تستطيع استخدام extract epoch. وستحتاج بدلًا من ذلك إلى استخدام شيء مثل:

select 	extract(day from ts.int)*60*60*24 +
	extract(hour from ts.int)*60*60 + 
	extract(minute from ts.int)*60 +
	extract(second from ts.int)
	from
		(select timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00' as int) ts

وهذا، كما ترى، بشع إلى حد كبير. وإن كنت تخطط لكتابة SQL تعمل على منصات متعددة، فسأفكر في امتلاك مكتبة من الدوال المعرّفة من المستخدم الشائعة لكل نظام إدارة قواعد بيانات، تتيح لك توحيد أي متطلبات شائعة كهذه. فهذا يُبقي شيفرتك الأساسية أنظف بكثير.

تلميح: يمكنك فعل ذلك باستخراج الـ epoch من الفترة بين طابعين زمنيين.

6. حساب عدد الأيام في كل شهر من أشهر 2012

السؤال

لكل شهر من أشهر السنة في 2012، أخرج عدد الأيام في ذلك الشهر. ونظّم الإخراج في عمود من الأعداد الصحيحة يحتوي رقم الشهر في السنة، وعمود ثانٍ يحتوي نوع بيانات فترة (interval).

النتائج المتوقعة

month	length
1	31 days
2	29 days
3	31 days
4	30 days
5	31 days
6	30 days
7	31 days
8	31 days
9	30 days
10	31 days
11	30 days
12	31 days

الإجابة

select 	extract(month from cal.month) as month,
	(cal.month + interval '1 month') - cal.month as length
	from
	(
		select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
	) cal
order by month;

تعرض هذه الإجابة عدة مفاهيم تعلمناها. فنستخدم دالة GENERATE_SERIES لإنتاج طوابع زمنية تغطي سنة كاملة، بزيادة شهر واحد في كل مرة. ثم نستخدم دالة EXTRACT للحصول على رقم الشهر. وأخيرًا، نطرح كل طابع زمني + شهر واحد منه.

ومن الجدير بالملاحظة أن طرح طابعين زمنيين سينتج دائمًا فترة بالأيام (أو أجزاء من يوم). ولن تحصل على إجابة بالأشهر أو السنوات فقط، لأن طول هاتين الفترتين متغير.

تلميح: طرح طابعين زمنيين سيعطيك الفترة التي تبحث عنها. ويمكنك استخدام دالة EXTRACT للحصول على الشهر من طابع زمني.

7. حساب عدد الأيام المتبقية في الشهر

السؤال

لأي طابع زمني معطى، احسب عدد الأيام المتبقية في الشهر. ويجب أن يُحسب اليوم الحالي يومًا كاملًا، أيًا كان الوقت. واستخدم '2012-02-11 01:00:00' طابعًا زمنيًا للمثال بغرض بناء الإجابة. ونظّم الإخراج كقيمة فترة واحدة.

النتائج المتوقعة

remaining
19 days

الإجابة

select (date_trunc('month',ts.testts) + interval '1 month') 
		- date_trunc('day', ts.testts) as remaining
	from (select timestamp '2012-02-11 01:00:00' as testts) ts

نجم هذه الفقرة هو دالة DATE_TRUNC. وهي تفعل إلى حد كبير ما تتوقعه — فهي تقتطع تاريخًا عند دقيقة أو ساعة أو يوم أو شهر معيّن، وهكذا. وطريقة حلّنا هذه المسألة هي اقتطاع طابعنا الزمني لمعرفة الشهر الذي نحن فيه، وإضافة شهر إلى ذلك، ثم طرح طابعنا الزمني. ولضمان معاملة الأيام الجزئية كأيام كاملة، يُقتطع الطابع الزمني الذي نطرحه عند أقرب يوم.

ولاحظ طريقتنا في وضع الطابع الزمني داخل استعلام فرعي. وهذا ليس مطلوبًا، لكنه يعني أنك تستطيع إعطاء الطابع الزمني اسمًا، بدلًا من تكرار القيمة الحرفية مرارًا.

تلميح: ألقِ نظرة على دالة DATE_TRUNC

8. حساب وقت انتهاء الحجوزات

السؤال

أرجع قائمة بأوقات البدء والانتهاء لآخر 10 حجوزات (مرتّبة بحسب وقت انتهائها، ثم بحسب وقت بدئها) في النظام.

النتائج المتوقعة

starttime	endtime
2013-01-01 15:30:00	2013-01-01 16:00:00
2012-09-30 19:30:00	2012-09-30 20:30:00
2012-09-30 19:00:00	2012-09-30 20:30:00
2012-09-30 19:30:00	2012-09-30 20:00:00
2012-09-30 19:00:00	2012-09-30 20:00:00
2012-09-30 19:00:00	2012-09-30 20:00:00
2012-09-30 18:30:00	2012-09-30 20:00:00
2012-09-30 18:30:00	2012-09-30 20:00:00
2012-09-30 19:00:00	2012-09-30 19:30:00
2012-09-30 18:30:00	2012-09-30 19:30:00

الإجابة

select starttime, starttime + slots*(interval '30 minutes') endtime
	from cd.bookings
	order by endtime desc, starttime desc
	limit 10

يعيد هذا السؤال ببساطة وقت بدء الحجز، ووقت انتهاء محسوبًا يساوي وقت البدء + (30 دقيقة * slots). ولاحظ أنه لا بأس إطلاقًا في ضرب الفترات.

والأمر الآخر الذي ستلاحظه هو استخدام order by وlimit للحصول على آخر عشرة حجوزات. وكل ما يفعله ذلك هو ترتيب الحجوزات بحسب وقت انتهائها (تنازليًا)، وانتقاء أول عشرة.

تلميح: يمكنك ضرب فترة بعدد الشرائح في الحجز.

9. إرجاع عدد الحجوزات لكل شهر

السؤال

أرجع عدد الحجوزات لكل شهر، مرتّبة بحسب الشهر

النتائج المتوقعة

month	count
2012-07-01 00:00:00	658
2012-08-01 00:00:00	1472
2012-09-01 00:00:00	1913
2013-01-01 00:00:00	1

الإجابة

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

هذا السؤال إعادة استخدام بسيطة إلى حد كبير لمفاهيم رأيناها من قبل. فنحن ببساطة نعُدّ عدد الحجوزات، ونجمّع بحسب وقت بدء الحجز، مقتطعًا عند الشهر.

تلميح: ستحتاج على الأرجح إلى دالة date_trunc مرة أخرى.

10. حساب نسبة الاستغلال لكل مرفق بحسب الشهر

السؤال

احسب نسبة الاستغلال لكل مرفق بحسب الشهر، مرتّبة بحسب الاسم والشهر، ومقرّبة إلى منزلة عشرية واحدة. وقت الافتتاح 8 صباحًا، ووقت الإغلاق 8:30 مساءً. ويمكنك معاملة كل شهر كشهر كامل، بغض النظر عن وجود تواريخ كان النادي فيها مغلقًا.

النتائج المتوقعة

name	month	utilisation
Badminton Court	2012-07-01 00:00:00	23.2
Badminton Court	2012-08-01 00:00:00	59.2
Badminton Court	2012-09-01 00:00:00	76.0
Massage Room 1	2012-07-01 00:00:00	34.1
Massage Room 1	2012-08-01 00:00:00	63.5
Massage Room 1	2012-09-01 00:00:00	86.4
Massage Room 2	2012-07-01 00:00:00	3.1
Massage Room 2	2012-08-01 00:00:00	10.6
Massage Room 2	2012-09-01 00:00:00	16.3
Pool Table	2012-07-01 00:00:00	15.1
Pool Table	2012-08-01 00:00:00	41.5
Pool Table	2012-09-01 00:00:00	62.8
Pool Table	2013-01-01 00:00:00	0.1
Snooker Table	2012-07-01 00:00:00	20.1
Snooker Table	2012-08-01 00:00:00	42.1
Snooker Table	2012-09-01 00:00:00	56.8
Squash Court	2012-07-01 00:00:00	21.2
Squash Court	2012-08-01 00:00:00	51.6
Squash Court	2012-09-01 00:00:00	72.0
Table Tennis	2012-07-01 00:00:00	13.4
Table Tennis	2012-08-01 00:00:00	39.2
Table Tennis	2012-09-01 00:00:00	56.3
Tennis Court 1	2012-07-01 00:00:00	34.8
Tennis Court 1	2012-08-01 00:00:00	59.2
Tennis Court 1	2012-09-01 00:00:00	78.8
Tennis Court 2	2012-07-01 00:00:00	26.7
Tennis Court 2	2012-08-01 00:00:00	62.3
Tennis Court 2	2012-09-01 00:00:00	78.4

الإجابة

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

جوهر هذا الاستعلام (الاستعلام الفرعي الداخلي) بسيط جدًا حقًا: تجميع لحساب العدد الإجمالي للشرائح المستخدمة لكل مرفق في الشهر. وإن كنت قد غطّيت بقية هذا القسم وفئة التجميعات، فالأرجح أنك لم تجد هذا الجزء صعبًا كثيرًا.

لكن هذا الاستعلام، للأسف، فيه تعقيد آخر: حساب عدد الأيام في كل شهر. ويمكننا حساب عدد الأيام بين شهرين بطرح طابعين زمنيين بينهما شهر. وهذا يعطينا للأسف نوع بيانات فترة، لا يمكننا استخدامه في العمليات الحسابية. وفي هذه الحالة تجاوزنا هذا القيد بتحويل طوابعنا الزمنية إلى تواريخ قبل الطرح. فطرح نوعَي date يعطينا عددًا صحيحًا من الأيام.

وبديل هذا التحايل تحويل الفترة إلى قيمة epoch: أي عدد من الثواني. وللقيام بذلك استخدم EXTRACT(EPOCH FROM month)/(246060). وهذا على الأرجح أسلوب أجمل بكثير، لكنه أقل قابلية للنقل إلى أنظمة قواعد بيانات أخرى.

تلميح: تذكّر أن الأشهر متفاوتة الأطوال — ستحتاج إلى حساب عدد الشرائح المتاحة في كل شهر. وتحتاج إلى إيجاد طريقة لاسترجاع عدد الأيام في الشهر كعدد صحيح (لا كفترة).