Header Ads

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:

}); 


No comments

If you have any doubt, please let me know.

Powered by Blogger.