How to Extract Date & Time from a createdAt Field in Sequelize with Timezone Handling

When you're working with timestamps in databases, you'll often need to format dates properly and convert them to specific time zones for accurate reporting or display.

If you're using Sequelize with PostgreSQL, here’s a clear and clean guide to get:

• Full timestamp in the desired timezone

• Only the date

• Only the time

• Day of the week
  — all directly in your query!

Let’s dive in.

---

📌 1. Native SQL Approach

If you're writing raw SQL, here’s how you can convert the createdAt timestamp to a specific timezone:

SELECT *, 
  "createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev' AS "createdAt" 
FROM users 
WHERE id = 1;

Or using variables:

SELECT *, 
  "createdAt"::timestamptz AT TIME ZONE :timezone AS "createdAt" 
FROM users 
WHERE id = :id;

✅ Explanation:

• ::timestamptz AT TIME ZONE 'Europe/Kiev' converts the timestamp to the desired timezone.

• :timezone and :id are bind variables for safer queries.

---

📌 2. Sequelize Approach (Model-Based)

Here’s how you can achieve the same thing in Sequelize, using attributes and sequelize.literal() for timezone conversion.

const user = await User.findOne({
  where: { id: 1 },
  attributes: {
    include: [
      [
        sequelize.literal(`"User"."createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'`),
        'createdAt'
      ]
    ]
  }
});

✅ Explanation:

• sequelize.literal() allows raw SQL in Sequelize queries.

• We’re converting the createdAt timestamp to the 'Europe/Kiev' timezone.

---

📌 3. Advanced: Formatting Date, Time, and Day of Week

You can also extract and format parts of the date, like day, date, time, etc.

attributes: [
  [sequelize.col('account.bankName'), 'User Bank Account'],

  // Day of the week (Mon, Tue, etc.)
  [
    sequelize.fn("TO_CHAR", sequelize.col("transaction.createdAt"), "DY"),
    'Day'
  ],

  // Transaction date (e.g., 10-Apr-2025)
  [
    sequelize.fn("TO_CHAR", sequelize.col("transaction.createdAt"), "dd-Mon-yyyy"),
    'Transaction Date'
  ],

  // Transaction time in timezone
  [
    sequelize.literal(`TO_CHAR("transaction"."createdAt"::timestamptz AT TIME ZONE 'Asia/Calcutta', 'hh12:mi:ss AM')`),
    "Transaction Time"
  ],

  // JSON field extraction
  [
    sequelize.json('brandDetails.name'),
    'Retail Merchant'
  ],

  // Transaction amount
  ['amount', 'Transaction Amount (AED)'],

  // Round-up tiers (example JSON formatting)
  [
    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'
  ],

  // Calculated field
  [
    sequelize.literal('("roundUpAmount"::numeric - "amount"::numeric)'),
    'Roundup Amount (AED)'
  ],

  ['roundUpAmount', 'Nearest Roundup (AED)']
]

✅ Explanation:

• TO_CHAR formats the date/time parts.

• sequelize.literal() lets you inject timezone-aware SQL expressions.

• Clean and readable field aliases make your exported reports or APIs more understandable!

---

📌 4. Pro Tip: Using Variables to Prevent SQL Injection

When dynamically injecting time zones, always sanitize inputs to prevent SQL injection!

Example with variables:

const timeZone = 'Asia/Calcutta'; // Safe, validated timezone

attributes: [
  [
    sequelize.literal(`"transaction"."createdAt"::timestamptz AT TIME ZONE '${timeZone}'`),
    "Transaction Timezone"
  ]
]

Make sure your timeZone value comes from a safe, predefined list of allowed timezones.

---

✅ Summary

Use Case	SQL Syntax	Sequelize Syntax
Convert to timezone	"createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'	sequelize.literal(... AT TIME ZONE 'Europe/Kiev')
Format Date	TO_CHAR(..., 'dd-Mon-yyyy')	sequelize.fn("TO_CHAR", sequelize.col(...), 'dd-Mon-yyyy')
Format Time	TO_CHAR(..., 'hh12:mi:ss AM')	sequelize.literal(... TO_CHAR(..., 'hh12:mi:ss AM'))
Format Day	TO_CHAR(..., 'DY')	sequelize.fn("TO_CHAR", sequelize.col(...), 'DY')

---

Final Thoughts

Handling dates and times in different time zones can be tricky, but with the right combination of Sequelize, Postgres functions, and a bit of sequelize.literal(), you can have clean, accurate, and well-formatted outputs for all your needs 🚀