CREATETABLEtable_time(idserialprimarykey,class_namevarchar(10)notnull,start_timetimenotnull,end_timetimenotnull);INSERTINTOtable_time(class_name,start_time,end_time)VALUES('maths','08:00:00','08:55:00'),('chemistry','08:55:00','09:00:00');SELECT*FROMtable_time;-- OUTPUTid|class_name|start_time|end_time----+------------+------------+----------1|maths|08:00:00|08:55:002|chemistry|08:55:00|09:00:00(2rows)SELECTCURRENT_TIME;current_time--------------------07:21:00.163354+00(1row)SELECTCURRENT_TIME(2);current_time----------------07:21:14.96+00(1row)SELECTLOCALTIME;localtime-----------------07:21:36.717509(1row)SELECTtime'12:10'-time'04:30'asRESULT;result----------07:40:00(1row)-- format : interval 'n type'-- n = number-- type : second, minute, hours, day, month, year ....SELECTCURRENT_TIME,CURRENT_TIME+INTERVAL'2 hours'asRESULT;current_time|result--------------------+--------------------07:22:06.241919+00|09:22:06.241919+00(1row)SELECTCURRENT_TIME,CURRENT_TIME+INTERVAL'-2 hours'asRESULT;current_time|result--------------------+--------------------07:22:16.644727+00|05:22:16.644727+00(1row)
Timestamp and Timezone
timestamp : stores time without time zone
timestamptz : timestamp with time zone , stored using UTC format
adding timestamp to timestamptz without mentioning the zone will result in server automatically assumes timezone to system's timezone
Internally, PostgreSQL will store the timezoneaccurately but then OUTPUTting the data, will it be converted according to your timezone
PostgreSQL doesn't provide internal function to generate UUID's, use uuid-ossp
CREATEEXTENSIONIFNOTEXISTS"uuid-ossp";SELECTuuid_generate_v1();uuid_generate_v1--------------------------------------4d459e0c-fb3e-11eb-a638-0242ac110002-- pure randomnessSELECTuuid_generate_v4();uuid_generate_v4--------------------------------------418f39e5-8a46-4da2-8cea-884904f45d6fCREATETABLEproducts_uuid(iduuiddefaultuuid_generate_v1(),product_namevarchar(100)notnull);INSERTINTOproducts_uuid(product_name)VALUES('ice cream'),('cake'),('candies');SELECT*FROMproducts_uuid;id|product_name--------------------------------------+--------------5cf1dbe0-fb3e-11eb-a638-0242ac110002|icecream5cf1df28-fb3e-11eb-a638-0242ac110002|cake5cf1df46-fb3e-11eb-a638-0242ac110002|candiesCREATETABLEproducts_uuid_v4(iduuiddefaultuuid_generate_v4(),product_namevarchar(100)notnull);INSERTINTOproducts_uuid_v4(product_name)VALUES('ice cream'),('cake'),('candies');SELECT*FROMproducts_uuid_v4;learning=#SELECT*FROMproducts_uuid_v4;id|product_name--------------------------------------+--------------83b74bed-2cf8-4e26-80b0-c7c7b2e5f3e7|icecreamac563251-7a95-408d-966b-ed5ecc1f228d|cake1079f6d3-b0c3-40ef-bd2e-da4467b63432|candies
It is better to use these types instead of plain text types of store network address, because these types offer input error checking and specialised operators and functions
Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.
Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.
A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!
On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.
Top comments (0)