Date : When we are used Date data type it is take 4 byte to store date value .It can mange lowest and highest data date .
Date : When we are used Date data type it is take 4 byte to store date value .It can mange lowest and highest data date . Example:
(b) Extract YEAR MONTH DAY
select extract(YEAR from '2021-01-01'::DATE);
select extract(MONTH from '2021-01-01'::DATE);
select extract(DAY from '2021-01-01'::DATE );
(c) Extract hour minute second
select extract( hour from timestamp '2021-09-09 23:50:50');
select extract(minute from timestamp '2021-09-25 23:50:50');
select extract(second from timestamp '2021-09-25 23:50:55');
(D) CREATE TABLE FOR UNDERSTAND LIVE FETCH DATA
DROP TABLE IF EXISTS history;
CREATE TABLE history (
document_id serial PRIMARY KEY,
header_text VARCHAR (255) NOT NULL,
posting_date DATE NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO history (header_text) VALUES('Billing to customer XYZ');
select * from history ;
#CREATE TABLE WITHOUT TIMEZONE INSERT DEFAULT DATE TIME
drop table if exists document_data;
create table document_data
(
id uuid default uuid_generate_v4(),
Doc_type text,
datadate DATE default current_date,
cur_datadate varchar default to_char(now(),'yyyy-mm-dd hh:mm:ss')
)
Current date time :-
SELECT NOW(); Output : "2021-09-08 23:22:47.689135+05:30"
SELECT NOW()::date;
Another way you get current data using it :
SELECT CURRENT_DATE;
Note :The result is in the format: yyyy-mm-dd.
yyyy-mm-dd. TO_CHAR(): It is function you can change date format
SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');
Output:"15/09/2021"
SELECT TO_CHAR(NOW() :: DATE, 'yyyy-dd-mm');
Output:"2021-15-09"
SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');
Output:"Sep 15, 2021"
Get the interval between two dates :
SELECT first_name,last_name, '1989-01-15' - birth_date as diff
FROM employees;
OUTPUT :
"Shannon"; "Freeman"; 14
"Sheila"; "Wells"; 3997
"Ethel"; "Webb"; 5128
Note: Now I want to convert date in year month day
select first_name,last_name,EXTRACT (YEAR FROM birth_date) as YEAR
from employees
select first_name,last_name,EXTRACT (MONTH FROM birth_date) as MONTH
from employees
select first_name,last_name,EXTRACT (DAY FROM birth_date) as MONTH
from employees
PostgreSQL provides you with two temporal data types for handling timestamp:
timestamp: a timestamp without timezone one.timestamptz: timestamp with a timezone.
CREATE TABLE demo (
ts TIMESTAMP,
tstz TIMESTAMPTZ
);
INSERT INTO demo (ts, tstz)
VALUES('2021-06-22 19:10:25-07','2021-06-22 19:10:25-07');
Note :- How to check timezone :
#SHOW TIMEZONE;
"Asia/Kolkata"
Note : Now i want to change timezone
# SET timezone = 'America/Los_Angeles';
TimeZone
---------------------
America/Los_Angeles
(1 row)
DATE TIME INTERVAL :
select now() CUR_TIME ,now()::DATE + INTERVAL '8 hours 30 minutes' CUR_DAY_DATE,
now()::DATE + INTERVAL '1 DAY 8 hours 20 minutes' NEXT_DAY_DATE ;
OUTPUT :
CUR_TIME CUR_DAY_DATE NEXT_DAY_DATE "2021-09-16 11:34:33 ";"2021-09-16 08:30:00" ; "2021-09-17 08:20:00"
SELECT
INTERVAL '2h 50m' + INTERVAL '10m'; OUTPUT : -- 03:00:00
SELECT
INTERVAL '2h 50m' - INTERVAL '50m'; OUTPUT :-- 02:00:00
SELECT 600 * INTERVAL '1 minute'; OUTPUT :-- 10:00:00
TO_CHAR():we can convert time format
SELECT TO_CHAR( '2021-01-01', 'yyyy/mm/dd' );
Output : 2021/01/01
SELECT TO_CHAR( INTERVAL '17h 20m 05s', 'HH24:MI:SS' ); OUTPUT:"17:20:05"
EXTRACT DATA:- you can extract any thing from time like hour ,day,mint
SELECT EXTRACT ( MINUTE FROM INTERVAL '5 hours 21 minutes' );
OUTPUT : 21 minutes
SELECT EXTRACT ( HOUR FROM INTERVAL '5 hours 21 minutes' );
OUTPUT :5 Hour
SELECT EXTRACT ( HOUR FROM timestamp '2021-09-14 23:15:00' );
SELECT EXTRACT ( MINUTE FROM timestamp '2021-09-14 23:15:00');
SELECT EXTRACT ( DAY FROM timestamp '2021-09-14 23:15:00');
TIME Data Type
CREATE TABLE OFFICE_TIME (
id serial PRIMARY KEY,
shift_name VARCHAR NOT NULL,
start_at TIME NOT NULL,
end_at TIME NOT NULL
);
INSERT INTO OFFICE_TIME(shift_name, start_at, end_at)
VALUES('Morning', '08:00:00', '12:00:00'),
('Afternoon', '13:00:00', '17:00:00'),
('Night', '18:00:00', '22:00:00');
SELECT * FROM OFFICE_TIME;
SELECT CURRENT_TIME;
timetz
--------------------
00:51:02.746572-08
(1 row)
SELECT CURRENT_TIME(5);
current_time
-------------------
00:52:12.19515-08
(1 row)
SELECT LOCALTIME;
localtime
-----------------
00:52:40.227186
(1 row)
SELECT LOCALTIME(0);
localtime
----------
00:56:08
(1 row)
SELECT LOCALTIME AT TIME ZONE 'UTC-7';
timezone
--------------------
16:02:38.902271+07
(1 row)
SELECT
LOCALTIME,
EXTRACT (HOUR FROM LOCALTIME) as hour,
EXTRACT (MINUTE FROM LOCALTIME) as minute,
EXTRACT (SECOND FROM LOCALTIME) as second,
EXTRACT (milliseconds FROM LOCALTIME) as milliseconds;
Adjusting interval values
There are two functions justifydays and justifyhours or justify_interval that allows you to adjust the interval of 30-day as one month and the interval of 24-hour as one day .
SELECT
justify_days(INTERVAL '30 days');
Output : "1 mon";
SELECT justify_hours(INTERVAL '24 hours')
Output : "1 Day";
SELECT
justify_interval(interval '1 year -1 hour');
Output:"11 mons 29 days 23:00:00"
PostgreSQL interval output format:
1.SET intervalstyle = 'sql_standard'; Output:- +6-5 +4 +3:02:01
2.SET intervalstyle = 'postgres'; Output:- 6 years 5 mons 4 days 03:02:01
3.SET intervalstyle = 'iso_8601'; Output :- 6Y5M4DT3H2M1S
4.SET intervalstyle = 'postgres_verbose'; Output:- @ 6 years 5 mons 4 days 3 hours 2 mins 1 sec
SELECT CURRENT_TIMESTAMP;
Output :"2021-09-08 23:24:28.936336+05:30"
SELECT TIMEOFDAY();
Output :"Wed Sep 08 23:24:34.574011 2021 IST"
SELECT CURRENT_DATE + '1 HOUR'::INTERVAL;
Output : "2021-09-08 01:00:00"
SELECT CURRENT_DATE + '1 DAY'::INTERVAL ;
Output :"2021-09-09 00:00:00"
SELECT CURRENT_DATE + '-1 DAY'::INTERVAL;
Output :"2021-09-07 00:00:00"
SELECT CURRENT_TIMESTAMP + INTERVAL '1 day';
Output :"2021-09-09 23:25:18.033729+05:30"
SELECT TIMEOFDAY();
Output : "Wed Sep 08 23:23:07.400700 2021 IST"
SHOW TIMEZONE;
Output : "Asia/Kolkata"
SELECT timezone('America/New_York','2021-09-08 00:00');
Output : "2021-09-07 14:30:00"
No comments:
Post a Comment