Geek asks and answers

PostgreSQL time handling

I have a table with fields dateand time. I find it difficult to understand how I deal with this, in part, because I do not understand how time can be converted into a number. I made a table using the following command:

CREATE TABLE tracking.time_record
(
  date date, 
  "time" time without time zone,
  id character(100)
)

An example of my data:

"2012-04-18" | "18:33:19.612" | "2342342384"

How to run a query so that I can check all the values idthat have a time value > 10 pmon a specific day, for example?

I understand that since my time is stored in a character type variable, something like this does not work:

SELECT * FROM tracking.time_record 
WHERE "time" > "04:33:38.884" AND date > "2012-04-18"

(This is my first time and date tracking study - I probably should have chosen different column names)

+3
source share
3 answers

.

, , , . PostgreSQL .

:
: "MyColumn" - ( , ,..), . < > : 'string literal'.

PostgreSQL.

, date time . SQL PostgreSQL. .

timestamp date time: character(100) , -, id. . text varchar:

:

CREATE TABLE tbl (
   tbl_id text CHECK(length(id) <= 100)
 , ts timestamp
);

time date, , :

SELECT ts::time AS the_time, ts::date AS the_date FROM tbl;

date_trunc() extract() .
... id, > 10 :

SELECT *
FROM   tbl
WHERE  ts::time > '22:00'
AND    ts::date = '2012-04-18';

:

...
WHERE  ts > '2012-04-18 22:00'::timestamp
AND    ts < '2012-04-19 00:00'::timestamp;

ts .

timestamp PostgreSQL:

+10

date_part,

select id 
from tracking.time_record
where date_part('hour', time) >= 22
  and date = '2012-04-18'

The >= 22 > 22, 10:00 11:00. 10:00:00. , .

.

+4

comparing time and date values ​​should work fine in postgresql, just make sure you convert your strings to the appropriate types:

SELECT * FROM tracking.time_record 
WHERE "time" > time '04:33:38.884' AND "date" > date '2012-04-18'
+3
source

More articles:


All Articles