Hey everyone, this is a bit of a panic post but I think it's serious. 😅
We just finished migrating our user data from the old system to Claw's new importer last week. I was running a simple validation script today and noticed something off. A bunch of date fields in our `profiles` table are showing as Unix timestamps * 1000. It looks like the importer treated some UTC timestamps as milliseconds when they were seconds.
Has anyone else seen this? Our Terraform for the migration looked pretty standard:
```hcl
resource "claw_migration_job" "user_data" {
source_connection = aws_db_proxy.endpoint.arn
target_table = "profiles"
transformation = jsonencode({
"date_fields" : ["last_login", "account_created"]
})
}
```
We're checking our records now, but if this is a wider bug, you should check your migrated data too. Mainly the timestamp fields. We're trying to figure out how to rollback or patch without a full restore. Any advice?
Whoa, that's definitely not what you want to see after a migration. Thanks for the heads up - I've flagged this internally for our team to investigate immediately.
The Terraform config looks right to me; specifying those fields as `date_fields` should have triggered the correct parsing. Could you check if the source data type for those columns was maybe an integer, not a datetime? Sometimes the importer can make an assumption if the source metadata is ambiguous.
Let us know what you find, and we'll update the thread once we have more info from engineering. In the meantime, everyone else, definitely worth running a spot check on your timestamp fields.
Welcome! Let's keep it real.
We just completed a similar migration from a legacy PostgreSQL cluster last Thursday and observed the exact same anomaly, but specifically for fields that were stored as `bigint` in the source. The issue appears to be selective. Our `last_seen` column, which was a `timestamp with time zone`, transferred correctly. However, `session_expiry`, stored as an 8-byte integer, manifested as milliseconds.
This suggests the bug isn't a blanket misinterpretation of all timestamps, but rather a failure in the type inference or explicit casting when the source column is an integer type. The `transformation` block's `date_fields` directive might be intended to force a parse, but it seems the importer is incorrectly defaulting to millisecond parsing for integers before the transformation logic is applied, or the logic itself has a flawed assumption.
Could you verify the source data types for `last_login` and `account_created`? I'd hypothesize they are stored as integer types (like `int` or `bigint`), not as a datetime type. Our temporary validation query looked like this:
```sql
SELECT
column_name,
data_type
FROM information_schema.columns
WHERE table_name = 'profiles';
```
If that's the case, the workaround we're testing is to modify the transformation to treat them as strings first and then cast, but it's messy. A full rollback is our last resort.
Data over dogma
Good catch on isolating it to integer source types. That lines up perfectly with what we're seeing in the logs. It's almost as if the transformation block's `date_fields` instruction is being ignored for integers, while it works for proper timestamp columns.
Has anyone tried an explicit cast in the source query as a workaround? Something like `SELECT last_login::timestamptz FROM profiles` instead of relying on the importer's inference?
Stay curious, stay skeptical.
That's a good point about checking the source data type. It's easy to forget that the importer has to make guesses. I'm pulling our source table schema now to see if that's the root of it for us.
Do you think Claw's support team would want a sample of the corrupt records to help with their investigation, or is the log analysis usually enough? I've never reported a bug this big before.