How to do a query of DAY_ONLY on a DateTime field?-CodePudding

I have a entity like this

import { Field, ObjectType } from "type-graphql";
import {
  BaseEntity,
  Column,
  CreateDateColumn,
  Entity,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
} from "typeorm";
import { Brand } from "./Brand";
import { Price } from "./Price";
import { UserComment } from "./UserComment";

@ObjectType()
@Entity()
export class Product extends BaseEntity {
  @Field()
  @PrimaryGeneratedColumn()
  id!: number;

  //...

  @Field()
  @CreateDateColumn()
  createdAt: Date;
}

In postgresql database, createdAt is stored like this

2022-04-06 18:13:18.141658

Example , i want to get all product of today, How can i do it?

CodePudding user response：

In postgreSQL we can convert to date by suffixing with ::date.
Here createdAt is datatype timestamp.
This article treats the subject in greater depth https://wanago.io/2021/03/15/postgresql-typeorm-date-time/

select
  createdAt AsDateTime,
  createdAt::date AsDate
from myTable;

asdatetime          | asdate    
:------------------ | :---------
2022-02-01 10:11:15 | 2022-02-01

db<>fiddle here

CodePudding user response：

Maybe you can try CAST(createdAt AS DATE) as create_at_date

Or if you want to break it down further you can use

Date_Part('year', createdat) as Year
Date_part('month', createdat) as month
Date_part('day', createdDate) as day

use this if you want to do calculation for your quarterly sales or monthly sales.