{"id":705,"date":"2012-04-03T10:10:41","date_gmt":"2012-04-03T08:10:41","guid":{"rendered":"http:\/\/oracle.malin.pl\/?p=705"},"modified":"2012-04-03T10:58:40","modified_gmt":"2012-04-03T08:58:40","slug":"sql-daty-operacje-na-datach-funkcje-na-dtach","status":"publish","type":"post","link":"https:\/\/oracle.malin.pl\/?p=705","title":{"rendered":"SQL &#8211; daty, operacje na datach, wbudowane funkcje"},"content":{"rendered":"<p>Funkcje SQL pobieraj\u0105ce bie\u017c\u0105c\u0105 dat\u0119:<\/p>\n<pre lang=\"PLSQL\">SELECT\r\n\r\nCURRENT_DATE , -- czas sesji, typ DATE\r\n\r\nCURRENT_TIMESTAMP , -- czas sesji, typ TIMESTAMP WITH TIME ZONE\r\n\r\nLOCALTIMESTAMP , -- czas sesji, typ TIMESTAMP\r\n\r\nSYSDATE , -- czas serwera, typ DATE\r\n\r\nSYSTIMESTAMP -- czas serwera, typ TIMESTAMP WITH TIME ZONE\r\n\r\nFROM dual;<\/pre>\n<p>Do konwersji dat na tekst i formatowania s\u0142u\u017cy funkcja <strong>TO_CHAR.<\/strong>\u00a0Przyk\u0142ady:<strong><!--more--><\/strong><\/p>\n<pre lang=\"PLSQL\">SELECT\r\n\r\nTO_CHAR(SYSDATE, 'Day, DDth Month YYYY' ), --Wtorek , 03RD Kwiecie\u0144 2012,\r\n\r\nTO_CHAR(SYSDATE, 'Day, DDth Month YYYY' , 'NLS_DATE_LANGUAGE=Romanian' ), --Mar\u0163i , 03RD Aprilie 2012\r\n\r\nTO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY' ), --Wtorek, 3RD Kwiecie\u0144 2012 -- FM - usuwa poprzedzaj\u0105ce 0 przd dniem\r\n\r\nTO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) --2012-04-03 10:21:22\r\n\r\nFROM dual<\/pre>\n<p>Odwrotno\u015bci\u0105 funkcji TO_CHAR jest <strong>TO_DATE<\/strong> lub <strong>TO_TIMESTAMP<\/strong>.<\/p>\n<p>Do pobrania warto\u015bci okre\u015blonego segmentu daty s\u0142u\u017cy funkcja <strong>EXTRACT<\/strong>. Przyk\u0142ady<\/p>\n<pre lang=\"PLSQL\">SELECT\r\n\r\nEXTRACT(YEAR FROM SYSDATE), -- 2012\r\nEXTRACT(DAY FROM SYSDATE) -- 3\r\n\r\nfrom dual;<\/pre>\n<p>Funkcja <strong>TRUNC<\/strong> przycina dat\u0119 do okre\u015blonej jednostki. Przyk\u0142ady:<\/p>\n<pre lang=\"PLSQL\">select\r\n\r\nTRUNC(SYSDATE), -- ustawia godzin\u0119 na 00:00:00\r\nTRUNC(SYSDATE, 'MM'), -- pierwszy dzie\u0144 miesi\u0105ca\r\nTRUNC(SYSDATE, 'Q'), -- pierwszy dzie\u0144 kwartalu\r\nTRUNC(SYSDATE, 'Y') -- pierwszy dzie\u0144 roku\r\n\r\nFROM dual;<\/pre>\n<p>Inne ciekawe wbudowane funkcje to<strong> ADD_MONTHS, NEXT_DAY, LAST_DAY.<\/strong><\/p>\n<pre lang=\"PLSQL\">SELECT\r\n\r\nADD_MONTHS(SYSDATE, 5),   -- dodaje 5 misi\u0119cy do daty\r\nADD_MONTHS(SYSDATE, -5),  -- odejmuje 5 misi\u0119cy od daty\r\nNEXT_DAY(SYSDATE,'Czw'),  -- najbli\u017cszy czwartek\r\nLAST_DAY(SYSDATE)         -- ostani dzie\u0144 miesi\u0105ca\r\n\r\nFROM dual;<\/pre>\n<p>Operacje arytmetyczne na datach (w dniach):<\/p>\n<pre lang=\"PLSQL\">SELECT \r\n\r\nSYSDATE + 7,  -- data \"za tydzie\u0144\"\r\nSYSDATE - 1,  -- wczoraj\r\nSYSDATE - 2\/24,   -- dwie godziny wstecz\r\nsysdate - trunc(sysdate,'MM')  -- dni od pocz\u0105tku miesi\u0105ca\r\n\r\nFROM dual;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Funkcje SQL pobieraj\u0105ce bie\u017c\u0105c\u0105 dat\u0119: SELECT CURRENT_DATE , &#8212; czas sesji, typ DATE CURRENT_TIMESTAMP , &#8212; czas sesji, typ TIMESTAMP WITH TIME ZONE LOCALTIMESTAMP , &#8212; czas sesji, typ TIMESTAMP SYSDATE , &#8212; czas serwera, typ DATE SYSTIMESTAMP &#8212; czas &hellip; <a href=\"https:\/\/oracle.malin.pl\/?p=705\">Czytaj dalej <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":38,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[88,97],"tags":[105],"_links":{"self":[{"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/705"}],"collection":[{"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=705"}],"version-history":[{"count":9,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/705\/revisions"}],"predecessor-version":[{"id":707,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/705\/revisions\/707"}],"wp:attachment":[{"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}