Formatting a Postgres timestamp in Editor

DNSinSC

I am sorry if this has already been asked, but I can't seem to find the answer, and I have searched for two hours. I have an issue trying to use Editor with a Postgres DB timestamp field. Postgres formats the timestamp field with ms like this:

2023-01-12 13:57:12.323

My Editor PHP validation uses this code, so it will not display a Postgres timestamp because of the extra digits. The field is blank:

Field::inst( 'date' )
->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
)

I would like to just drop the extra digits on display and just show Y-m-d or Y-m-d H:i:s, but I cannot seem to make Editor do what I want. I either get a blank field or the full unmodified length.

Does anyone have a moment to give me some pointers?

allan

Hi,

You could use:

Field::inst( 'date' )
  ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
  ->getFormatter( Format::datetime( 'Y-m-d H:i:s.v', 'Y-m-d H:i:s' ) )
  ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s.v' ) )
)

That said, I've been trying to figure out why you are seeing this problem and I'm not with my Postgres install. What PHP and Postgres versions are you using? Also, can you show me the CREATE TABLE for the table in question?

Allan

DNSinSC

Hi Allan. Thank you. I am using Debian 11 Bullseye, PHP 7.4, Postgres 13.8. The date field is a timestamp. Here is the schema:

CREATE TABLE public.funfact (
    id int4 NOT NULL DEFAULT nextval('funfact_id_seq'::regclass),
    fact text NULL,
    "date" timestamp NULL,
    CONSTRAINT funfact_pkey PRIMARY KEY (id)
);

I have a trigger in Postgres to set the date to "NOW();" when the record is updated, as I couldn't get Editor to add the date on "new" and update the date on "edit." I am sure there is a way to do that! But a DB trigger solved it. That said, Editor wouldn't display the field for me. I will try the code you posted.

Function:

CREATE OR REPLACE FUNCTION public.update_date_column()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
NEW.date = now();
RETURN NEW;
END;
$function$
;

Trigger on table:

create trigger update_funfact_date before
update
    on
    public.funfact for each row execute function update_date_column()

The date field in the DB looks like this:
2023-01-12 17:40:41.745
2023-01-12 18:39:52.773
2023-01-12 17:27:09.153

DNSinSC

Postgres creates this date when updating the timestamp with now();
2023-01-12 17:40:41.745

I tested it, but for me, this code displays a blank date field when given that date format:

Editor::inst($db, 'funfact', 'id')
    ->fields(
        Field::inst('id'),
        Field::inst('fact'),
        Field::inst('date')->set(Field::SET_CREATE)
            ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
            ->getFormatter( Format::datetime( 'Y-m-d H:i:s.v', 'Y-m-d H:i:s' ) )
            ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s.v' ) )
    )
    ->on('preCreate', function ($editor, &$values) {
        $editor
            ->field('date')
            ->setValue(date("Y-m-d H:i:s"));
    })
    ->process($_POST)
    ->json();

Perhaps if I can make Editor generate the date on CREATE and on EDIT for the same field, I could just use that instead of a Postgres DB trigger. Thank you so much for your help. I am enjoying Editor and look forward to doing great things with it once I get past the learning curve!

allan

Perhaps if I can make Editor generate the date on CREATE and on EDIT for the same field

I'd actually say that is a good use of database triggers. It ensures data consistency, regardless of how the data is inserted in the database table. However, if you want to do it in PHP, using a server-side event to set the value as you have done, is the right way to do it.

I tested it, but for me, this code displays a blank date field when given that date format:

Could you show me what happens if you remove the get and set formatters - i.e. what does the unformatted data look like?

Allan

DNSinSC

The "date" field straight from the DB with no validate, get, or set, which is the way I am currently having to use it, is displayed like this:

2023-01-13 09:51:04.872367

allan

Ah - I think you want u rather than v in that case. u is microseconds, while v is milliseconds.

I'm still not understanding why you are getting this issue and I'm not though!

Allan

DNSinSC

Thank you for your help, Allan. I finally resolved it by making the Postgresql function drop the extra information with "date_trunc." Now I can display it with Editor as a normal timestamp: 2023-01-16 10:29:00

Function:
CREATE OR REPLACE FUNCTION public.update_date_column()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
  NEW.date = date_trunc('minute', now());
  RETURN NEW;
END;
$function$
;

Table Trigger:
create trigger update_tablename_date before
update
on