I have been trying to simulate the following Oracle statement in PostgreSQL:
SELECT TRUNC(SYSDATE)
To reach this, I was already able to simulate the TRUNC() function receiving only one time datatype parameter, which is timestamp without time zone. This is how I made it:
CREATE OR REPLACE FUNCTION public.trunc(teste TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE
LANGUAGE plpgsql
AS
$BODY$
BEGIN
RETURN DATE_TRUNC('DAY', teste);
END
$BODY$
language 'plpgsql';
It works just fine when I try to call this function passing the CLOCK_TIMESTAMP() function as an argument but casting it at the same time. Just like this:
SELECT TRUNC(CLOCK_TIMESTAMP()::TIMESTAMP WITHOUT TIME ZONE);
So that I can get around this whole statement, I'd like to make it more practical replacing this entire argument for SYSDATE. I've already tried to create the same SYSDATE function in PostgreSQL:
CREATE OR REPLACE FUNCTION public.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
BEGIN
RETURN DATE_TRUNC('SECOND', CLOCK_TIMESTAMP() AT TIME ZONE '<<My_timezone>>');
END
$BODY$
language plpgsql;
Well... I'm not gonna say it does not work, because it does. Nevertheless, everytime I call the TRUNC() function created by me, I need to pass SYSDATE as an argument using parentheses:
SELECT TRUNC(SYSDATE());
That is, although it is similar from what I am trying to simulate, it isn't syntactically correct. Would there be a way to take off the parentheses from it while calling SYSDATE function?
Futhermore, is there any observation to be done about what I am trying to do? It's important to quote that the timestamp without time zone was chosen by purpose, although SYSDATE returns DATE type by default.
select current_date;? See dbfiddle.uk/…timestamp without time zoneby default. That's why those functions and casting were made as being of this type, moreover, the central idea is to use the keywordSYSDATE.current_datewould achieve that.