Retrieve date (only) column as plain string without added time information (or custom type, in general)

some-user123 commented 3 years ago

Problem

I'm having a date column in PostgreSQL. I.e. it only contains the date, no time, no timezone. In Prisma schema I have to define:

date         DateTime  @db.Date

In my API I only want to specify the date without time. I have to write a lot of nasty transformation, each time this column is read from or written to the database, e.g.

return await prisma.entity.findMany().map(e => ({ ...e, date: e.date.toISOString().substring(0,10) }))

Alternative: Transformer

An even more powerful alternative would be to allow transformers (e.g. using class-transformer) for each column to be specified, that are executed when transferring data from and to the database. This would allow users to have their columns transformed to the type they like to work with.

Alternative: Middleware

It was suggested (https://stackoverflow.com/questions/66109340/retrieve-dateonly-column-as-string-or-map-to-string-without-time-using-prisma) to use middlewares for this purpose. While this works in principle, it breaks the type checking. And the strong type checking is one of the biggest advantages of Prisma over alternative solutions. So, I don't want to lose that.

Example:

    prisma.$use(async (params, next) => {
      if (
        params.model === 'Model' &&
        params.action === 'findMany'
      ) {
        const result = await next(params)
        result.forEach((element) => {
          if (element.date) {
            element.date = element.date.toISOString().substring(0, 10)
          }
        })
        return result
      }
      return next(params)
    })

prisma.model.findMany will still tell you the date property is Date while it actually is String. Also it is tedious to write transformation for all operations, where this model might be included. I'd prefer to declare this once for a column.

Additional context

This problem might be applicable to a wide range of column types where it would be nice to handle it in a different type than Prisma suggests. E.g. users could want to handle Datetime as instances of MomentJs or DayJs instead of native Date. Or they would like to handle Decimal as String or Number or BigJs. They might have custom implementations db.Xml, inet, cidr, ...

Please let me know, if I should add more information etc. Or there are simple solutions, I might have missed :)

chinanderm commented 3 years ago

I would love to have this as well in for MySQL. The Date column type is extremely useful when time isn't a factor.

jhnns commented 3 years ago

I think Prisma should completely avoid JavaScript's native Date object and treat dates as regular strings (or at least Date ISO strings).

Converting dates to instances of Date sometimes adds unwanted precision:

new Date("2021-01-01").toISOString() // '2021-01-01T00:00:00.000Z'

2021-01-01 is timezone agnostic and carries a different date information than 2021-01-01T00:00:00.000Z

There are also other issues caused by this behavior: https://github.com/prisma/prisma/issues/7490 https://github.com/prisma/prisma/issues/4355

yasmikash commented 2 years ago

Is there any solution to this issue yet?

Phosphorus-M commented 2 years ago

Is there any solution to this issue yet?

Same question and problem

edzis commented 2 years ago

I am still hoping for a clean usage scenario. 🙏

To work around this I started converting the input and output of prisma queries. And to help deal with the extra mess on various date formats I started using the approach communicated in https://github.com/freewind-demos/typescript-typing-string-literal-template-iso-date-demo/blob/master/hello.ts

export type IsoDate = `20${'0' | '1' | '2'}${Digit}-${Month}-${Day}`
export type IsoDateLong = `20${'0' | '1' | '2'}${Digit}-${Month}-${Day}T00:00:00.000Z`

import { z } from "zod";
import { IsoDate, IsoDateLong } from "./IsoDate";

export const $IsoDate = z.string()
  .regex(/^\d\d\d\d-\d\d-\d\d$/, 'not valid IsoDate')
  .transform(x => x as IsoDate);
export const $IsoDateLong = z.string()
  .regex(/^\d\d\d\d-\d\d-\d\dT00:00:00\.000Z$/, 'not valid IsoDateLong')
  .transform(x => x as IsoDateLong);

export function getIsoDate(input: IsoDateLong | Date): IsoDate {
  if (typeof input === 'string') {
    if (process.env.NODE_ENV === 'development') {
      $IsoDateLong.parse(input)
    }
    return input.substring(0, 10) as IsoDate;
  }
  return input.toISOString().substring(0, 10) as IsoDate;
};

export function getIsoDateLong(input: IsoDate | Date): IsoDateLong {
  if (typeof input === 'string') {
    if (process.env.NODE_ENV === 'development') {
      $IsoDate.parse(input)
    }
    return (input + 'T00:00:00.000Z') as IsoDateLong;
  }
  return input.toISOString() as IsoDateLong;
};

jacobgoh101 commented 1 year ago

On my end, this causes problems & confusion when parsing time in the browser.

I had to work around it by adding a Prisma middleware to sanitize the date values.

import { PrismaClient } from "@prisma/client";
import { formatISO } from "date-fns";
import { flatten, unflatten } from "flat";
import _ from "lodash";

export const prisma = new PrismaClient();

// Prisma middleware to sanitize date values
prisma.$use(async (params, next) => {
  let result = await next(params);

  if (_.isArray(result) || _.isObject(result))
    result = sanitizeDateValuesInObject(result);

  return result;
});

/**
 *
 * @param result any object
 * @returns object
 * This function remove problematic time component from date only values. It converts Date object to YYYY-MM-DD.
 * example of @db.Date column result 1995-05-17T00:00:00.000Z
 */
function sanitizeDateValuesInObject(result: any): any {
  if (_.isArray(result))
    return result.map((r) => sanitizeDateValuesInObject(r));
  if (!_.isObject(result)) return result;
  result = flatten(result);
  result = _.mapValues(result, (value, key) => {
    if (
      value instanceof Date &&
      value.toISOString().endsWith("T00:00:00.000Z")
    ) {
      return formatISO(value, { representation: "date" });
    }
    return value;
  });
  result = unflatten(result);
  return result;
}

FernandoAcTr commented 1 year ago

I did a transformer in order to get primitives values of prisma model objects

export function toPrimitives<T extends {}>(object: T): Primitives<T> {
  const keys = Object.keys(object);
  const oldObject: any = object;
  const newObject: any = {};

  keys.forEach((key) => {
    if (oldObject[key] instanceof Date) newObject[key] = oldObject[key].toISOString();
    else if (oldObject[key] instanceof Array) {
      newObject[key] = oldObject[key].map((x: any) =>
        x instanceof Date
          ? x.toISOString()
          : x instanceof Decimal
          ? x.toNumber()
          : x instanceof Object
          ? toPrimitives(x)
          : x
      );
    } else if (oldObject[key] instanceof Decimal) newObject[key] = oldObject[key].toNumber();
    else if (oldObject[key] instanceof Object) newObject[key] = toPrimitives(oldObject[key]);
    else newObject[key] = oldObject[key];
  });

  return newObject;
}

And Primitives is a custom Type object

import { Decimal } from '@prisma/client/runtime';

type Methods<T> = {
  [P in keyof T]: T[P] extends Function ? P : never;
}[keyof T];

type MethodsAndProperties<T> = { [key in keyof T]: T[key] };

type Properties<T> = Omit<MethodsAndProperties<T>, Methods<T>>;

export type Primitives<T extends {}> = {
  [key in keyof Properties<T>]: T[key] extends string
    ? T[key]
    : T[key] extends number
    ? T[key]
    : T[key] extends Date
    ? string
    : T[key] extends Date | null
    ? string | null
    : T[key] extends Array<Object>
    ? Array<Primitives<T[key][number]>>
    : T[key] extends Decimal
    ? number
    : T[key] extends Object
    ? Primitives<T[key]>
    : T[key];
};

This way I can do const myObject = await prisma.anything.findFirst() const myPrimitiveObject = usePrimitives(myObject) and myPrimitiveObject contains an exactly copy of myObject but Dates are string and Decimal are number. And Primitives type helps me to not lose the types.

I'm not sure if this is a good way at all, but It works for me

jonbergan commented 1 year ago

+1 for this also.

eddieliccardo commented 1 year ago

+1

doylio commented 1 year ago

+1

sbsg35 commented 1 year ago

I used computed fields for this, it doesn't seem to break type checking.

model Appointment {
  ...
  date DateTime @db.Date
  ...
}

import { format } from "date-fns";
import { PrismaClient } from "@prisma/client";

const prismaClient = new PrismaClient().$extends({
  result: {
    appointment: {
      dateShort: { // this is the computed field.
        needs: { date: true },
        compute({ date }) {
          return format(date, "MMM yyyy"); 
        },
      },
    },
  },
});

const appointmentList = async () => {
  const data = await prismaClient.appointment.findFirst();
  console.log(appointment.date); // 2023-06-01T00:00:00.000Z
  console.log(appointment.dateShort); // 'Jun 2023'
};

vbg8exadel commented 4 months ago

I used computed fields for this, it doesn't seem to break type checking. Actually it does. In your case you adding additional field so it is fine. But if you would try to transform existing field as shown here: https://www.prisma.io/blog/client-extensions-preview-8t3w27xkrxxn#example-transformed-fields then you'll get Date in Prisma generated classes but 'string' in the findFirst or any other finder. The only way it should be fixed is if Prisma team would add @transform element to the schema syntax.

prisma / prisma