Calculate a Year’s Chinese Zodiac Animal

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited February 5 in Vertica Tips

The Chinese Zodiac (Sheng Xiao) is based on a twelve-year cycle where each year is represented by an animal sign. The twelve animal signs in order are the rat, ox, tiger, rabbit, dragon, snake, horse, sheep, monkey, rooster, dog and pig. The animal sign is calculated according to the Chinese lunar calendar.

I can never remember which animal sign is used to represent the new year, so I created a simple function in Vertica to remind me!

Example:

dbadmin=> CREATE OR REPLACE FUNCTION chinese_zodiac_animal (x INT) RETURN VARCHAR(7)
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN SPLIT_PART('Rat,Ox,Tiger,Rabbit,Dragon,Snake,Horse,Sheep,Monkey,Rooster,Dog,Pig', ',', (((x-4)%12)::INT)+1);
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT chinese_zodiac_animal(2019) "HAPPY CHINESE NEW YEAR!!!!";
HAPPY CHINESE NEW YEAR!!!!
----------------------------
Pig
(1 row)

dbadmin=> SELECT chinese_zodiac_animal(2005) "Vertica was founded in the Year of the...";
Vertica was founded in the Year of the...
-------------------------------------------
Rooster
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/SPLIT_PART.htm

Have fun!

Sign In or Register to comment.