Calculate a Year’s Chinese Zodiac Animal: Quick Tip

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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm

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

Have fun!