how to get date,time from date field or createdAt field in sequelize with timeZone
let attributes = [
in SQL:SELECT *, "createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev' AS "createdAt" FROM users WHERE id = 1;
# or with variables
sequelize.findOne({
where: { id: 1 },
attributes: {
include: [
[sequelize.literal(`"User"."createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'`), 'createdAt'],
// also you can use variables, of course remember about SQL injection:
// [sequelize.literal(`"User"."updatedAt"::timestamptz AT TIME ZONE ${timeZoneVariable}`), 'updatedAt'],
]
}
[Sequelize.col('account.bankName'), 'User Bank Account'],
[sequelize.fn("TO_CHAR", sequelize.col("transaction.createdAt"), "DY"),'Day'],
[sequelize.fn("TO_CHAR", sequelize.col("transaction.createdAt"), "dd-Mon-yyyy"),'Transaction Date'],
//[sequelize.fn("TO_CHAR", sequelize.col("transaction.createdAt"), "hh12:mi:ss AM"),'Transaction Time'],
[Sequelize.literal(`TO_CHAR("transaction"."createdAt" ::timestamptz AT TIME ZONE 'Asia/Calcutta', 'hh12:mi:ss AM')`), "Transaction Time"],
[sequelize.json('brandDetails.name'), 'Retail Merchant'],
['amount', 'Transaction Amount (AED)'],
[Sequelize.literal(`case when "transaction"."roundupTiers" IS NULL then NULL else concat(json_extract_path_text(array_to_json("transaction"."roundupTiers"), '0','roundOffAmount'), '/', json_extract_path_text(array_to_json("transaction"."roundupTiers"), '1','roundOffAmount'), '/',json_extract_path_text(array_to_json("transaction"."roundupTiers"), '2','roundOffAmount')) end`), 'Round-Up Tier'],
[Sequelize.literal('("roundUpAmount"::numeric - "amount"::numeric)'), 'Roundup Amount (AED)'],
['roundUpAmount', 'Nearest Roundup (AED)']
]
SELECT *, "createdAt"::timestamptz AT TIME ZONE :timezone AS "createdAt" FROM users WHERE id = :id;
For Sequelize (for User model) it will be something like this:
});
Post a Comment