postgres compare time extracted from a timestamp

Question

postgres compare time extracted from a timestamp

Navigation

#1 by (0 votes)

0

I need to extract the hours and minutes of a timestamp in mysql, I tried to use maketime ();

CREATE OR REPLACE FUNCTION agendar(dia timestamp) returns bool as 
$$
declare

hora time := extract(hour from dia);
minuto time := extract(minute from dia);

horaminuto := maketime(hora,minuto,0);

but accuses of incorrect syntax. I need to extract the date and time to compare and see if the timestamp schedule has reached a time limit or not. For example, a trade that opens at 9:30 in the morning, I need to check if the time that was passed in the timestamp happens before or after that time

sql postgresql plpgsql

asked by anonymous 13.03.2017 / 03:16

1 answer

Problems with reading the database [SQLite] [closed] A Model with 2 relationships but in the view only 1

score 0 · Answer 1

I did not quite understand its function, where it returns the value, where it declares variable, comparison ... I'm accustomed to procedures rather than functions, maybe so, but come on, maybe it'll help you a bit to resolve without create a function.

You can extract the times through this function:

SELECT DATE_FORMAT(datetimeVariable, '%H') AS 'hora' FROM tabela

% H means the hour flag, for example, to extract minutes would be the % i flag:

SELECT DATE_FORMAT(datetimeVariable, '%i') AS 'minuto' FROM tabela

Now if you want a simpler way to compare directly, try this function here:

SELECT Count(*) AS 'valido' FROM tabelaHorarios
WHERE TIME_TO_SEC(TIME(primeiroDatetime)) > TIME_TO_SEC('09:30:00') AND
id='1'

What happens is, it will return 0 if firstDatetime did not last past 9:30, otherwise a value greater than 1, for how much data it find ... the id you define which table value you want to test ... Why this? Probably your system will compare 1 month of the trade, to see on what days it closed, right? You can set a range of the entire month and count how many days of the month the trade opened after 9:30 am ...