Use postgreSQL processing time - PostgreSQL, Processing, time, use

I have a table with date and time fields. I have a hard time understanding how I deal with this problem, partly because I don’t understand how time is converted to a number. I use the following The command created a table:

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

The example of my data is as follows:

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

How to run a query so that I can check all id values with a time value>. For example, 10 o’clock in the evening on a certain day?

I realized that because 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 exploration of time/date tracking-I You should choose a different column name)

So far, neither of these two answers caught you The actual problem.

Although the explicit deduction of the appropriate type is certainly not harmed, it is not necessary. PostgreSQL automatically coerces the string literal to the appropriate type.

Your problem stems from a basic syntax error:
Double quotes are used for identifiers: “MyColumn”-only necessary for other illegal identifiers (mixed case, reserved words,…), and should be avoided at the beginning.
Single quotes are used for values:’string literal’.

You may be interested in the well-written chapters of identifiers and constants in the PostgreSQL manual.

Although we are here, do not use date or time as column names. In every SQL standard it is reserved words, in PostgreSQL it is type name. This will lead to confusing code And error message.

I recommend using only a timestamp column instead of a separate date and time:
And you almost certainly don’t want the character (100) to be a data type – especially for the id column. This type of blank filling is basically just for historical reasons. Please consider using text or varchar:

> Any downsides of using data type “text” for storing strings?

It seems Like this:

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

Delivered to the time or date you only need these components, it Short and cheap:

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

Use date_trunc()< /code>or extract() to meet more specific needs. To query the time value>... id value at 10 pm on a certain day:



SELECT *
FROM tbl
WHERE ts::time> '22:00'
AND ts::date = '2012-04-18';
 < p>Or, for any continuous time period:

...
WHERE ts> '2012-04-18 22:00'::timestamp
AND ts <'2012-04-19 00:00'::timestamp;
The second form can better use the ordinary index on ts, and it will be better in the case of large tables. Fast.
More information about timestamp processing in PostgreSQL:
> Ignoring timezones altogether in Rails and PostgreSQL



 I have a table with date and time fields. I have trouble understanding how I dealt with this problem, partly because I don’t understand how time is converted to numbers. I created a table with the following command: 
 
CREATE TABLE tracking.time_record
(
 date date, 
 "time" time without time zone,
 id character(100) 
)
My data example is as follows:

"2012-04-18" | "18:33:19.612" | "2342342384 "
How to run a query so that I can check all id values with time value>. For example, 10 o'clock in the evening on a certain day? 
I realized that because 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 exploration of time/date tracking-I You should choose a different column name)

So far, neither of these two answers captures your actual question. 
 < p>Although the explicit deduction of the appropriate type is certainly not harmed, it is not necessary. PostgreSQL automatically casts string literals to the appropriate type.
Your problem stems from a basic syntax error:
Double quotation marks are used for identifiers: "MyColumn"-only necessary for other illegal identifiers (mixed case, reserved words, ...) and should be avoided at the beginning. 
Single quotation marks are used for values:'string literal'. 
You may be interested in the well-written chapters of identifiers and constants in the PostgreSQL manual.
Although we are here, But don't use date or time as column names. It is reserved words in every SQL standard, and type names in PostgreSQL. This will lead to confusing codes and error messages.
I It is recommended to use only a timestamp column instead of a separate date and time: 
And you almost certainly don’t want the character (100) to be a data type – especially for the id column. This type of blank padding is basically just for historical reasons .Please consider using text or varchar:
> Any downsides of using data type “text” for storing strings?
It looks like this:
 
CREATE TABLE tbl (
 tbl_id text CHECK(length(id) <= 100)
, ts timestamp
);
 The time or date of these components, it is short and cheap:

SELECT ts::time AS the_time, ts::date AS the_date FROM tbl;
Use date_trunc() or extract() to meet more specific needs.
To query the time value> … The id value is 10 pm on a certain day:

SELECT *
FROM tbl
WHERE ts::time> '22:00'
 AND ts::date = '2012-04-18';
Or, for any continuous time period:

...
WHERE ts> '2012-04-18 22:00'::timestamp
AND ts <'2012-04-19 00:00'::timestamp;
The second form is OK Better use of ordinary indexes on ts, and faster in the case of large tables.
More information about timestamp processing in PostgreSQL:
> Ignoring timezones altogether in Rails and PostgreSQL

Leave a Comment Cancel reply