1. Query the data
Query the data of the day
select * from table1 as n where n.created_time>=current_date;
Query yesterday's data
select * from table1 as n where n.created_time>=current_date-1 and n.created_time <current_date ;
2. Query monthly data
Query the data of the month
select * from table1 as n WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now()) and extract(MONTH FROM created_time) = extract(MONTH FROM now())
Query last month's data
select *
from table1 as n
where created_time >= date_trunc('month',current_date - interval '1' month)
and created_time < date_trunc('month',current_date)3. Query annual data
Query the data of the year
select * from table1 as n WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now()) ORDER BY created_time
Query last year's data
select *
from table1 as n
where created_time >= date_trunc('year',current_date - interval '1' year)
and created_time < date_trunc('year',current_date)4. Type conversion
1. Query one day: datetime type, it needs to be converted to date type. If the field you want to query is already date type, it does not need to be converted.
select t_create from table where t_create::date = to_date(‘2023-02-08', ‘YYYY-MM-DD');
Turn to timestamp type, query by range
select * from table where create_date >= ‘2023-01-08'::timestamp and create_date < ‘2023-02-08'::timestamp;
3. Timestamp Long to Timestamp
select TO_TIMESTAMP(1512490630)
Transfer data, you can only get year, month, day, and time, minutes, and seconds
select to_date(‘2023-01-28 12:55:05')
5. Current date select current_date
6. Time and minute values with time zones are selected current_time; you can also use current_time(precision) to round the result into bits within a quarter of a second, such as select current_time(2); corresponding to the value without time zone: select localtime;
7. Year, month, day, hour, minute and second values with time zone select current_timestamp; corresponding to the value without time zone: select localtimestamp;
Supplement: Time zone conversion
Sometimes time zone conversion is particularly useful for displaying specific times in different time zones. AT TIME ZONE provides this functionality, how does it do it? We will demonstrate in a transaction because the now() function always returns the same value in the same transaction, so we can easily see the difference displayed in different time zones at the same time.
postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
now
-------------------------------
2013-08-26 12:39:39.122218+02
postgres=# SELECT now() AT TIME ZONE 'GMT';
timezone
----------------------------
2013-08-26 10:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'GMT+1';
timezone
----------------------------
2013-08-26 09:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'PST';
timezone
----------------------------
2013-08-26 02:39:39.122218Summarize
This is the end of this article about PostgreSql date type processing. For more related PostgreSql date type processing content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!