In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe that it actually is a Friday. Regardless of what they see on their watches and calendars, it becomes a crucial task of the day to prove it. Prove it technically that it actually is a Friday.
This blog post is my attempt to share the proofs of existence of a “Friday”.
Using MySQL datetime function: dayname
MySQL [(Kedar)]> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Friday |
+----------------+
1 row in set (0.000 sec)
Running system command from MySQL prompt
MySQL [(Kedar)]> \! date +'%A'
Friday
MySQL [(Kedar)]>
Using ntpd service with shell script and cowsay
# Shell Script: ./believeitornot
>/tmp/sayit
speak_the_truth() {
clear
cat /tmp/sayit | cowsay -f tux
exit;
}
# pgrep -x chronyd>/dev/null && echo -e "Declaration: Sworn on NTP daemon.\n" > /tmp/sayit && ntpd=1 || ( echo "Can't trust time without ntpd" >> /tmp/sayit && speak_the_truth && exit 1; )
pgrep -x ntpd >/dev/null && echo -e "Declaration: Sworn on NTP daemon.\n" > /tmp/sayit && ntpd=1 || ( echo "Can't trust time without ntpd" >> /tmp/sayit && speak_the_truth && exit 1; )
speak_the_truth
sleep 1
if [ $ntpd == 1 ]; then
for day in Saturday Sunday Monday Tuesday Wednesday Thursday; do
sleep 1;
if [ `date +'%A'` != $day ]; then
echo -e "Is this $day: NO. \n" >> /tmp/sayit
speak_the_truth
else
echo -e "This is $day, not a Friday.\n" >> /tmp/sayit
speak_the_truth
exit;
fi
done;
sleep 1;
if ! [ `date +'%A'` != "Friday" ]; then
echo -e "Is this anything but Friday? - NO\n" >> /tmp/sayit
speak_the_truth
sleep 1;
echo -e "\n Thank NTPD it is Friday." >> /tmp/sayit
speak_the_truth
fi;
fi;
Note that this was done on Cent OS 7. For Cent OS 8 you might not get ntp and instead use chronyd.
Sample Execution and proving of Friday
_______________________________
< Can't trust time without ntpd >
-------------------------------
\
\
.--.
|o_o |
|:_/ |
// \ \
(| | )
/'\_ _/`\
\___)=(___/
___________________________________
/ Declaration: Sworn on NTP daemon. \
| |
| Is this Saturday: NO. |
| |
| Is this Sunday: NO. |
| |
| Is this Monday: NO. |
| |
| Is this Tuesday: NO. |
| |
| Is this Wednesday: NO. |
| |
| Is this Thursday: NO. |
| |
| Is this anything but Friday? - NO |
| |
\ Thank NTPD it is Friday. /
-----------------------------------
\
\
.--.
|o_o |
|:_/ |
// \ \
(| | )
/'\_ _/`\
\___)=(___/
Stored procedure in MySQL 8 using WITH RECURSIVE
DROP PROCEDURE IF EXISTS kedar.is_it_friday;
delimiter //
CREATE PROCEDURE kedar.is_it_friday(IN today varchar(15))
BEGIN
IF STR_TO_DATE(today,'%Y-%m-%d') IS NULL THEN
SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Are you insane?';
END IF;
WITH RECURSIVE `days` AS
(
SELECT 1 AS `day` UNION ALL SELECT `day` + 1 FROM `days` WHERE `day` < DAY(LAST_DAY(NOW()))
)
SELECT dt fridays_in_month, if(dt = date(now()),'Hell Yeah!!!','Nope!') is_it_today from (SELECT concat(year(today),'-',lpad(month(today),2,'0'),'-',lpad(day,2,'0')) dt FROM `days` where DAYNAME(concat(year(today),'-',lpad(month(today),2,'0'),'-',lpad(day,2,'0'))) ='FRIDAY') xXx;
END
//
delimiter ;Executing the procedure and validating proofs
mysql> call kedar.is_it_friday('2022-09-16');
+------------------+--------------+
| fridays_in_month | is_it_today |
+------------------+--------------+
| 2022-09-02 | Nope! |
| 2022-09-09 | Nope! |
| 2022-09-16 | Hell Yeah!!! |
| 2022-09-23 | Nope! |
| 2022-09-30 | Nope! |
+------------------+--------------+
5 rows in set (0.00 sec)List Fridays of the whole year using MySQL procedures
DROP PROCEDURE IF EXISTS kedar.list_annual_days;
delimiter //
CREATE PROCEDURE kedar.list_annual_days(IN year varchar(4), IN day varchar(10))
BEGIN
DECLARE dateStart date;
DECLARE dateEnd date;
DECLARE _query VARCHAR(1000);
IF day = '' THEN SET day='FRIDAY'; END IF;
IF year = '' THEN SET year=year(now()); END IF;
select day, year;
set dateStart=(select concat(year,'-01-01'));
set dateEnd=(select concat(year,'-12-31'));
DROP TABLE IF EXISTS days_8582;
CREATE TEMPORARY TABLE days_8582 (kdt varchar(20), kdy varchar(20));
WHILE dateStart <= dateEnd DO
INSERT INTO days_8582 select dateStart, DAYNAME(dateStart) from dual where DAYNAME(dateStart) = day;
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
select kdt date, kdy day, if(kdt = date(now()),'Hell Yeah!!!','Nope!') is_it_today,if( datediff(kdt, now()) < 0,concat(abs( datediff(kdt, now())), ' days ago'), concat(abs( datediff(kdt, now())), ' days to go')) `when` from days_8582;
DROP TABLE IF EXISTS days_8582;
END
//
delimiter ;
Executing the procedure and validating proofs
mysql [kedar]> call kedar.list_annual_days('','');
+--------+------+
| day | year |
+--------+------+
| FRIDAY | 2022 |
+--------+------+
1 row in set (0.001 sec)
+------------+--------+--------------+---------------+
| date | day | is_it_today | when |
+------------+--------+--------------+---------------+
| 2022-01-07 | Friday | Nope! | 259 days ago |
| 2022-01-14 | Friday | Nope! | 252 days ago |
| 2022-01-21 | Friday | Nope! | 245 days ago |
| 2022-01-28 | Friday | Nope! | 238 days ago |
| 2022-02-04 | Friday | Nope! | 231 days ago |
| 2022-02-11 | Friday | Nope! | 224 days ago |
| 2022-02-18 | Friday | Nope! | 217 days ago |
| 2022-02-25 | Friday | Nope! | 210 days ago |
| 2022-03-04 | Friday | Nope! | 203 days ago |
| 2022-03-11 | Friday | Nope! | 196 days ago |
| 2022-03-18 | Friday | Nope! | 189 days ago |
| 2022-03-25 | Friday | Nope! | 182 days ago |
| 2022-04-01 | Friday | Nope! | 175 days ago |
| 2022-04-08 | Friday | Nope! | 168 days ago |
| 2022-04-15 | Friday | Nope! | 161 days ago |
| 2022-04-22 | Friday | Nope! | 154 days ago |
| 2022-04-29 | Friday | Nope! | 147 days ago |
| 2022-05-06 | Friday | Nope! | 140 days ago |
| 2022-05-13 | Friday | Nope! | 133 days ago |
| 2022-05-20 | Friday | Nope! | 126 days ago |
| 2022-05-27 | Friday | Nope! | 119 days ago |
| 2022-06-03 | Friday | Nope! | 112 days ago |
| 2022-06-10 | Friday | Nope! | 105 days ago |
| 2022-06-17 | Friday | Nope! | 98 days ago |
| 2022-06-24 | Friday | Nope! | 91 days ago |
| 2022-07-01 | Friday | Nope! | 84 days ago |
| 2022-07-08 | Friday | Nope! | 77 days ago |
| 2022-07-15 | Friday | Nope! | 70 days ago |
| 2022-07-22 | Friday | Nope! | 63 days ago |
| 2022-07-29 | Friday | Nope! | 56 days ago |
| 2022-08-05 | Friday | Nope! | 49 days ago |
| 2022-08-12 | Friday | Nope! | 42 days ago |
| 2022-08-19 | Friday | Nope! | 35 days ago |
| 2022-08-26 | Friday | Nope! | 28 days ago |
| 2022-09-02 | Friday | Nope! | 21 days ago |
| 2022-09-09 | Friday | Nope! | 14 days ago |
| 2022-09-16 | Friday | Nope! | 7 days ago |
| 2022-09-23 | Friday | Hell Yeah!!! | 0 days to go |
| 2022-09-30 | Friday | Nope! | 7 days to go |
| 2022-10-07 | Friday | Nope! | 14 days to go |
| 2022-10-14 | Friday | Nope! | 21 days to go |
| 2022-10-21 | Friday | Nope! | 28 days to go |
| 2022-10-28 | Friday | Nope! | 35 days to go |
| 2022-11-04 | Friday | Nope! | 42 days to go |
| 2022-11-11 | Friday | Nope! | 49 days to go |
| 2022-11-18 | Friday | Nope! | 56 days to go |
| 2022-11-25 | Friday | Nope! | 63 days to go |
| 2022-12-02 | Friday | Nope! | 70 days to go |
| 2022-12-09 | Friday | Nope! | 77 days to go |
| 2022-12-16 | Friday | Nope! | 84 days to go |
| 2022-12-23 | Friday | Nope! | 91 days to go |
| 2022-12-30 | Friday | Nope! | 98 days to go |
+------------+--------+--------------+---------------+
52 rows in set (0.020 sec)Let’s verifying against all time-zones and verify all are having Friday
[kedar@nitty-witty.com ~]# for tz in `timedatectl list-timezones`; do echo $tz has `date --date='TZ="$tz" this Fri' +'%A'`; done
Africa/Abidjan has Friday
Africa/Accra has Friday
Africa/Addis_Ababa has Friday
Africa/Algiers has Friday
Africa/Asmara has Friday
Africa/Bamako has Friday
Africa/Bangui has Friday
Africa/Banjul has Friday
Africa/Bissau has Friday
Africa/Blantyre has Friday
Africa/Brazzaville has Friday
Africa/Bujumbura has Friday
Africa/Cairo has Friday
Africa/Casablanca has Friday
Africa/Ceuta has Friday
Africa/Conakry has Friday
Africa/Dakar has Friday
Africa/Dar_es_Salaam has Friday
Africa/Djibouti has Friday
Africa/Douala has Friday
Africa/El_Aaiun has Friday
Africa/Freetown has Friday
Africa/Gaborone has Friday
Africa/Harare has Friday
Africa/Johannesburg has Friday
Africa/Juba has Friday
Africa/Kampala has Friday
Africa/Khartoum has Friday
Africa/Kigali has Friday
Africa/Kinshasa has Friday
Africa/Lagos has Friday
Africa/Libreville has Friday
Africa/Lome has Friday
Africa/Luanda has Friday
Africa/Lubumbashi has Friday
Africa/Lusaka has Friday
Africa/Malabo has Friday
Africa/Maputo has Friday
Africa/Maseru has Friday
Africa/Mbabane has Friday
Africa/Mogadishu has Friday
Africa/Monrovia has Friday
Africa/Nairobi has Friday
Africa/Ndjamena has Friday
Africa/Niamey has Friday
Africa/Nouakchott has Friday
Africa/Ouagadougou has Friday
Africa/Porto-Novo has Friday
Africa/Sao_Tome has Friday
Africa/Tripoli has Friday
Africa/Tunis has Friday
Africa/Windhoek has Friday
America/Adak has Friday
America/Anchorage has Friday
America/Anguilla has Friday
America/Antigua has Friday
America/Araguaina has Friday
America/Argentina/Buenos_Aires has Friday
America/Argentina/Catamarca has Friday
America/Argentina/Cordoba has Friday
America/Argentina/Jujuy has Friday
America/Argentina/La_Rioja has Friday
America/Argentina/Mendoza has Friday
America/Argentina/Rio_Gallegos has Friday
America/Argentina/Salta has Friday
America/Argentina/San_Juan has Friday
America/Argentina/San_Luis has Friday
America/Argentina/Tucuman has Friday
America/Argentina/Ushuaia has Friday
America/Aruba has Friday
America/Asuncion has Friday
America/Atikokan has Friday
America/Bahia has Friday
America/Bahia_Banderas has Friday
America/Barbados has Friday
America/Belem has Friday
America/Belize has Friday
America/Blanc-Sablon has Friday
America/Boa_Vista has Friday
America/Bogota has Friday
America/Boise has Friday
America/Cambridge_Bay has Friday
America/Campo_Grande has Friday
America/Cancun has Friday
America/Caracas has Friday
America/Cayenne has Friday
America/Cayman has Friday
America/Chicago has Friday
America/Chihuahua has Friday
America/Costa_Rica has Friday
America/Creston has Friday
America/Cuiaba has Friday
America/Curacao has Friday
America/Danmarkshavn has Friday
America/Dawson has Friday
America/Dawson_Creek has Friday
America/Denver has Friday
America/Detroit has Friday
America/Dominica has Friday
America/Edmonton has Friday
America/Eirunepe has Friday
America/El_Salvador has Friday
America/Fort_Nelson has Friday
America/Fortaleza has Friday
America/Glace_Bay has Friday
America/Goose_Bay has Friday
America/Grand_Turk has Friday
America/Grenada has Friday
America/Guadeloupe has Friday
America/Guatemala has Friday
America/Guayaquil has Friday
America/Guyana has Friday
America/Halifax has Friday
America/Havana has Friday
America/Hermosillo has Friday
America/Indiana/Indianapolis has Friday
America/Indiana/Knox has Friday
America/Indiana/Marengo has Friday
America/Indiana/Petersburg has Friday
America/Indiana/Tell_City has Friday
America/Indiana/Vevay has Friday
America/Indiana/Vincennes has Friday
America/Indiana/Winamac has Friday
America/Inuvik has Friday
America/Iqaluit has Friday
America/Jamaica has Friday
America/Juneau has Friday
America/Kentucky/Louisville has Friday
America/Kentucky/Monticello has Friday
America/Kralendijk has Friday
America/La_Paz has Friday
America/Lima has Friday
America/Los_Angeles has Friday
America/Lower_Princes has Friday
America/Maceio has Friday
America/Managua has Friday
America/Manaus has Friday
America/Marigot has Friday
America/Martinique has Friday
America/Matamoros has Friday
America/Mazatlan has Friday
America/Menominee has Friday
America/Merida has Friday
America/Metlakatla has Friday
America/Mexico_City has Friday
America/Miquelon has Friday
America/Moncton has Friday
America/Monterrey has Friday
America/Montevideo has Friday
America/Montserrat has Friday
America/Nassau has Friday
America/New_York has Friday
America/Nipigon has Friday
America/Nome has Friday
America/Noronha has Friday
America/North_Dakota/Beulah has Friday
America/North_Dakota/Center has Friday
America/North_Dakota/New_Salem has Friday
America/Nuuk has Friday
America/Ojinaga has Friday
America/Panama has Friday
America/Pangnirtung has Friday
America/Paramaribo has Friday
America/Phoenix has Friday
America/Port-au-Prince has Friday
America/Port_of_Spain has Friday
America/Porto_Velho has Friday
America/Puerto_Rico has Friday
America/Punta_Arenas has Friday
America/Rainy_River has Friday
America/Rankin_Inlet has Friday
America/Recife has Friday
America/Regina has Friday
America/Resolute has Friday
America/Rio_Branco has Friday
America/Santarem has Friday
America/Santiago has Friday
America/Santo_Domingo has Friday
America/Sao_Paulo has Friday
America/Scoresbysund has Friday
America/Sitka has Friday
America/St_Barthelemy has Friday
America/St_Johns has Friday
America/St_Kitts has Friday
America/St_Lucia has Friday
America/St_Thomas has Friday
America/St_Vincent has Friday
America/Swift_Current has Friday
America/Tegucigalpa has Friday
America/Thule has Friday
America/Thunder_Bay has Friday
America/Tijuana has Friday
America/Toronto has Friday
America/Tortola has Friday
America/Vancouver has Friday
America/Whitehorse has Friday
America/Winnipeg has Friday
America/Yakutat has Friday
America/Yellowknife has Friday
Antarctica/Casey has Friday
Antarctica/Davis has Friday
Antarctica/DumontDUrville has Friday
Antarctica/Macquarie has Friday
Antarctica/Mawson has Friday
Antarctica/McMurdo has Friday
Antarctica/Palmer has Friday
Antarctica/Rothera has Friday
Antarctica/Syowa has Friday
Antarctica/Troll has Friday
Antarctica/Vostok has Friday
Arctic/Longyearbyen has Friday
Asia/Aden has Friday
Asia/Almaty has Friday
Asia/Amman has Friday
Asia/Anadyr has Friday
Asia/Aqtau has Friday
Asia/Aqtobe has Friday
Asia/Ashgabat has Friday
Asia/Atyrau has Friday
Asia/Baghdad has Friday
Asia/Bahrain has Friday
Asia/Baku has Friday
Asia/Bangkok has Friday
Asia/Barnaul has Friday
Asia/Beirut has Friday
Asia/Bishkek has Friday
Asia/Brunei has Friday
Asia/Chita has Friday
Asia/Choibalsan has Friday
Asia/Colombo has Friday
Asia/Damascus has Friday
Asia/Dhaka has Friday
Asia/Dili has Friday
Asia/Dubai has Friday
Asia/Dushanbe has Friday
Asia/Famagusta has Friday
Asia/Gaza has Friday
Asia/Hebron has Friday
Asia/Ho_Chi_Minh has Friday
Asia/Hong_Kong has Friday
Asia/Hovd has Friday
Asia/Irkutsk has Friday
Asia/Jakarta has Friday
Asia/Jayapura has Friday
Asia/Jerusalem has Friday
Asia/Kabul has Friday
Asia/Kamchatka has Friday
Asia/Karachi has Friday
Asia/Kathmandu has Friday
Asia/Khandyga has Friday
Asia/Kolkata has Friday
Asia/Krasnoyarsk has Friday
Asia/Kuala_Lumpur has Friday
Asia/Kuching has Friday
Asia/Kuwait has Friday
Asia/Macau has Friday
Asia/Magadan has Friday
Asia/Makassar has Friday
Asia/Manila has Friday
Asia/Muscat has Friday
Asia/Nicosia has Friday
Asia/Novokuznetsk has Friday
Asia/Novosibirsk has Friday
Asia/Omsk has Friday
Asia/Oral has Friday
Asia/Phnom_Penh has Friday
Asia/Pontianak has Friday
Asia/Pyongyang has Friday
Asia/Qatar has Friday
Asia/Qostanay has Friday
Asia/Qyzylorda has Friday
Asia/Riyadh has Friday
Asia/Sakhalin has Friday
Asia/Samarkand has Friday
Asia/Seoul has Friday
Asia/Shanghai has Friday
Asia/Singapore has Friday
Asia/Srednekolymsk has Friday
Asia/Taipei has Friday
Asia/Tashkent has Friday
Asia/Tbilisi has Friday
Asia/Tehran has Friday
Asia/Thimphu has Friday
Asia/Tokyo has Friday
Asia/Tomsk has Friday
Asia/Ulaanbaatar has Friday
Asia/Urumqi has Friday
Asia/Ust-Nera has Friday
Asia/Vientiane has Friday
Asia/Vladivostok has Friday
Asia/Yakutsk has Friday
Asia/Yangon has Friday
Asia/Yekaterinburg has Friday
Asia/Yerevan has Friday
Atlantic/Azores has Friday
Atlantic/Bermuda has Friday
Atlantic/Canary has Friday
Atlantic/Cape_Verde has Friday
Atlantic/Faroe has Friday
Atlantic/Madeira has Friday
Atlantic/Reykjavik has Friday
Atlantic/South_Georgia has Friday
Atlantic/St_Helena has Friday
Atlantic/Stanley has Friday
Australia/Adelaide has Friday
Australia/Brisbane has Friday
Australia/Broken_Hill has Friday
Australia/Darwin has Friday
Australia/Eucla has Friday
Australia/Hobart has Friday
Australia/Lindeman has Friday
Australia/Lord_Howe has Friday
Australia/Melbourne has Friday
Australia/Perth has Friday
Australia/Sydney has Friday
Europe/Amsterdam has Friday
Europe/Andorra has Friday
Europe/Astrakhan has Friday
Europe/Athens has Friday
Europe/Belgrade has Friday
Europe/Berlin has Friday
Europe/Bratislava has Friday
Europe/Brussels has Friday
Europe/Bucharest has Friday
Europe/Budapest has Friday
Europe/Busingen has Friday
Europe/Chisinau has Friday
Europe/Copenhagen has Friday
Europe/Dublin has Friday
Europe/Gibraltar has Friday
Europe/Guernsey has Friday
Europe/Helsinki has Friday
Europe/Isle_of_Man has Friday
Europe/Istanbul has Friday
Europe/Jersey has Friday
Europe/Kaliningrad has Friday
Europe/Kiev has Friday
Europe/Kirov has Friday
Europe/Lisbon has Friday
Europe/Ljubljana has Friday
Europe/London has Friday
Europe/Luxembourg has Friday
Europe/Madrid has Friday
Europe/Malta has Friday
Europe/Mariehamn has Friday
Europe/Minsk has Friday
Europe/Monaco has Friday
Europe/Moscow has Friday
Europe/Oslo has Friday
Europe/Paris has Friday
Europe/Podgorica has Friday
Europe/Prague has Friday
Europe/Riga has Friday
Europe/Rome has Friday
Europe/Samara has Friday
Europe/San_Marino has Friday
Europe/Sarajevo has Friday
Europe/Saratov has Friday
Europe/Simferopol has Friday
Europe/Skopje has Friday
Europe/Sofia has Friday
Europe/Stockholm has Friday
Europe/Tallinn has Friday
Europe/Tirane has Friday
Europe/Ulyanovsk has Friday
Europe/Uzhgorod has Friday
Europe/Vaduz has Friday
Europe/Vatican has Friday
Europe/Vienna has Friday
Europe/Vilnius has Friday
Europe/Volgograd has Friday
Europe/Warsaw has Friday
Europe/Zagreb has Friday
Europe/Zaporozhye has Friday
Europe/Zurich has Friday
Indian/Antananarivo has Friday
Indian/Chagos has Friday
Indian/Christmas has Friday
Indian/Cocos has Friday
Indian/Comoro has Friday
Indian/Kerguelen has Friday
Indian/Mahe has Friday
Indian/Maldives has Friday
Indian/Mauritius has Friday
Indian/Mayotte has Friday
Indian/Reunion has Friday
Pacific/Apia has Friday
Pacific/Auckland has Friday
Pacific/Bougainville has Friday
Pacific/Chatham has Friday
Pacific/Chuuk has Friday
Pacific/Easter has Friday
Pacific/Efate has Friday
Pacific/Fakaofo has Friday
Pacific/Fiji has Friday
Pacific/Funafuti has Friday
Pacific/Galapagos has Friday
Pacific/Gambier has Friday
Pacific/Guadalcanal has Friday
Pacific/Guam has Friday
Pacific/Honolulu has Friday
Pacific/Kanton has Friday
Pacific/Kiritimati has Friday
Pacific/Kosrae has Friday
Pacific/Kwajalein has Friday
Pacific/Majuro has Friday
Pacific/Marquesas has Friday
Pacific/Midway has Friday
Pacific/Nauru has Friday
Pacific/Niue has Friday
Pacific/Norfolk has Friday
Pacific/Noumea has Friday
Pacific/Pago_Pago has Friday
Pacific/Palau has Friday
Pacific/Pitcairn has Friday
Pacific/Pohnpei has Friday
Pacific/Port_Moresby has Friday
Pacific/Rarotonga has Friday
Pacific/Saipan has Friday
Pacific/Tahiti has Friday
Pacific/Tarawa has Friday
Pacific/Tongatapu has Friday
Pacific/Wake has Friday
Pacific/Wallis has Friday
UTC has Friday
Verifying from cron
[root@ip-172-31-87-173 ~]# crontab -l
* * * * FRI echo "Damn right it is Friday." > can_you_believe_it
0 0 * * SAT rm -f can_you_believe_it
[root@ip-172-31-87-173 ~]# cat can_you_believe_it
Damn right it is Friday.
[root@ip-172-31-87-173 ~]#
Verifying from a website
[kedar@nitty-witty.com ~]$ curl -s -I kedar.vaijanapurkar.in | grep date | cut -c7-9
Fri
I’m open to more ideas as you know, doubters gonna doubt.