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
Top comments (0)