postgres compare time extracted from a timestamp

Asked

Viewed 577 times

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 whether or not the timestamp has reached a time limit. For example, a trade that opens at 9:30 am, I need to check if the time that was passed on the timestamp is past before or after that time

sql database postgresql function plpgsql

  • The signature of the make_time function is make_time(hour int, min int, sec double Precision) and returns a time. The signature of the Extract function is Extract(field from timestamp) and returns a double Precision. Therefore the time and minute variable declaration as time is incorrect, should be double Precision or even an int.

  • A question: you are working with Mysql (as in the text of your question) or with Postgresql (as it is in the tag)?

1 answer

0

I did not understand very well its function, where it returns the value, where declares variable, comparison... I’m used to procedures rather than functions, maybe so, but come on, maybe it’ll help you a little bit to solve without creating a function.

You can extract hours through this function:

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

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

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 the primeiroDatetime not past 9:30, otherwise a value greater than 1, for how much data he finds... the id you set which value of the table you want to test... Why is that? Probably your system will compare 1 month of trade, to see what days it closed, right? You can set a whole month range and count how many days of the month the trade opened after 9:30 am...

Browser other questions tagged sql database postgresql function plpgsql

You are not signed in. Login or sign up in order to post.