IMPORTANT for a very small group. I have come across a weird bug in time conversions when writing to and reading from a JDBC database. The context is as follows: I have datetime values that I want to write from Stata to a PostgreSQL database and then read back into a Stata program. Superficially this appears to work if, say, the variable dtime is defined as a %tc clock variable in Stata and as a timestamp variable in PostgreSQL. However, when I check the values carefully, the variable dtime stored in the data table has been incremented by 6 hours when being stored in the PostgreSQL table. In other words, the value "2025-03-15 00:00:000" in Stata becomes "2025-03-15 06:00:000" in the PostgreSQL table. This result is easily reproducible and it is not reversed when bringing the data back from PostgreSQL. I have tried doing the same conversion after defining dtime as a %tC variable. The behaviour is similar - i.e. an addition of 6 hours - but with 27 leap seconds added as should be expected.
Datetime conversions can be a complete nuisance. For those not familiar with datetime storage types in Stata clock %tc variables are stored as doubles and are the number of milliseconds since 1st January 1960. I believe that clock variables in PostgreSQL are stored as Unix datetime variables which are the number of milliseconds since 00.00.000 UTC on 1st January 1970. It looks as though someone got the adjustment from 1960 to 1970 off by 6 hours or 360,000 milliseconds. I think that most likely the problem is at the Stata end. I won't speculate why but I note that UTC is 6 hours ahead of Central Standard Time.
I hope that this can be fixed fairly soon! It is easy to implement a temporary fix, but inevitably this means changing the code to remove the fix at a later date.
Datetime conversions can be a complete nuisance. For those not familiar with datetime storage types in Stata clock %tc variables are stored as doubles and are the number of milliseconds since 1st January 1960. I believe that clock variables in PostgreSQL are stored as Unix datetime variables which are the number of milliseconds since 00.00.000 UTC on 1st January 1970. It looks as though someone got the adjustment from 1960 to 1970 off by 6 hours or 360,000 milliseconds. I think that most likely the problem is at the Stata end. I won't speculate why but I note that UTC is 6 hours ahead of Central Standard Time.
I hope that this can be fixed fairly soon! It is easy to implement a temporary fix, but inevitably this means changing the code to remove the fix at a later date.
Comment