Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Set timestamp columns in the database to timestamptz #323

Closed
redshiftzero opened this issue Nov 13, 2017 · 5 comments · Fixed by #978 or #989
Closed

Set timestamp columns in the database to timestamptz #323

redshiftzero opened this issue Nov 13, 2017 · 5 comments · Fixed by #978 or #989
Assignees
@michplunkett
Labels
feature-request size/M tech/python
Milestone
Backlog

Comments

@redshiftzero
Copy link
Member

redshiftzero commented Nov 13, 2017 •
edited
Loading

We are currently using naive datetime objects everywhere in this application, but we should be using timezone-aware datetimes in the application (e.g. using moment.js or similar) and store them in the database as UTC. We should not assume the timezone of the user.

From comment by @r4v5:

there’s a potential for discrepancy when initializing these with dates (at time 00:00) and being returned referencing the day before/after we intended.
@r4v5
Copy link
Contributor

r4v5 commented Nov 13, 2017

:(

@redshiftzero redshiftzero mentioned this issue Nov 13, 2017
Merged
3 tasks
@redshiftzero
Copy link
Member Author

---> :)

@redshiftzero redshiftzero added this to the Backlog milestone Jan 27, 2018
@michplunkett
Copy link
Collaborator

michplunkett commented Jun 23, 2023 •
edited
Loading

I talked to @abandoned-prototype today about this and the idea is to set all times in the DB to UTC. That should allow us localize times correctly in the future.

Context: Link

@michplunkett michplunkett changed the title Timezone support Set timestamp columns in the database to timestamptz Jun 23, 2023
@michplunkett michplunkett self-assigned this Jun 23, 2023
@michplunkett michplunkett mentioned this issue Jun 27, 2023
Closed
AetherUnbound pushed a commit that referenced this issue Jul 5, 2023
@sea-kelp
Create SECURITY.md (#323)
dc53ccb
@michplunkett
Copy link
Collaborator

michplunkett commented Jul 18, 2023 •
edited
Loading

Column name changes before updating data types:

  • descriptions: date_created -> created, date_updated -> updated
  • notes: date_created -> created, date_updated -> updated
  • raw_images: date_image_inserted: created, date_image_taken: taken

@michplunkett michplunkett mentioned this issue Jul 18, 2023
Merged
4 tasks
@michplunkett michplunkett linked a pull request Jul 18, 2023 that will close this issue
Change timestamp datatypes to timestamptz #978
Merged
4 tasks
michplunkett added a commit that referenced this issue Jul 24, 2023
@michplunkett @abandoned-prototype
Change timestamp datatypes to timestamptz (#978)
273adb2 
## Fixes issue
#323

## Description of Changes
Standardizing column names and changing `timestamp` datatypes to
`timestamptz`. To make sure the times were presented accurately, I
created a function that was called before any other ones to set the
`TIMEZONE` in the session object. From there, I used a filter that
applies the user's timezone to any dates that are presented. This
session object goes away after `60` minutes of no requests, though that
value is configurable should someone to change it.

The solution is essentially based off these two solutions:
- https://stackoverflow.com/a/75271114
- https://stackoverflow.com/a/49891626

Column name changes:
- `descriptions`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `notes`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `raw_images`: `date_image_inserted`: `created_at`, `date_image_taken`:
`taken_at`

As a `timestamp`:
<img width="1097" alt="Screenshot 2023-07-19 at 1 45 05 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/34ead7a9-e2de-43b7-a414-b50b7f0de0cb">

As a `timestamptz`:
<img width="1095" alt="Screenshot 2023-07-19 at 1 52 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/d6a34acb-aa98-43d7-929a-9d80dc01416c">

As a `timestamp` (testing the `downgrade` command):
<img width="1093" alt="Screenshot 2023-07-19 at 1 53 06 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/26c06464-1816-4ca2-8c6d-f5091f79dc9c">

## Screenshots
Description and Note `created_at` confirmation:
<img width="524" alt="Screenshot 2023-07-20 at 5 46 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/486e3690-2509-42db-89f2-990a89ea222c">

`descriptions.updated_at`: `2023-07-19 19:08:54.587185 +00:00` -> `July
19, 2023 2:08 PM`
`notes.updated_at`: `2023-07-19 19:06:51.464656 +00:00` -> `July 19,
2023 2:06 PM`

What it will actually look like:
<img width="525" alt="Screenshot 2023-07-20 at 5 55 19 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/5eb6a2d7-975f-4047-827c-1e71d0ff028a">

## Tests and linting
 - [x] This branch is up-to-date with the `develop` branch.
 - [x] `pytest` passes on my local development environment.
 - [x] `pre-commit` passes on my local development environment.
 - [x] Data-migration output:

<details><summary>Column Name Ouput</summary>

```shell
$ docker exec -it openoversight-web-1 bash
$ flask db stamp head
[2023-07-18 20:22:15,836] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
$ flask db migrate -m "standarize datetime field names"
[2023-07-18 20:27:19,780] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
...
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.taken_at'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_inserted' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_taken' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_taken_at' on '['taken_at']'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_inserted'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_taken'
  Generating /usr/src/app/OpenOversight/migrations/versions/2023-07-18-2027_07ace5f956ca_standarize_datetime_field_names.py ...  done
$ flask db upgrade
[2023-07-18 20:34:18,812] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
$
```
</details>

<details><summary>Datatype Output</summary>

```shell
% docker exec -it openoversight-web-1 bash
$ flask db stamp head
/usr/local/lib/python3.11/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
[2023-07-19 18:49:21,592] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> eb0266dc8588
$ flask db upgrade
[2023-07-19 18:49:46,272] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$ flask db downgrade
[2023-07-19 18:51:59,673] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1931b987ce0d -> 07ace5f956ca, convert timestamp to timestamptz
$ flask db upgrade
[2023-07-19 18:52:28,084] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$
```
</details>

---------

Co-authored-by: abandoned-prototype <[email protected]>
@michplunkett
Copy link
Collaborator

Done!

@michplunkett michplunkett mentioned this issue Jul 24, 2023
Closed
3 tasks
@michplunkett michplunkett linked a pull request Jul 24, 2023 that will close this issue
Modify TIMEZONE setting function #987
Closed
3 tasks
@michplunkett michplunkett mentioned this issue Jul 24, 2023
Merged
3 tasks
@michplunkett michplunkett linked a pull request Jul 24, 2023 that will close this issue
Add additional /timezone test #989
Merged
3 tasks
abandoned-prototype pushed a commit that referenced this issue Jul 25, 2023
@michplunkett
Add additional /timezone test (#989)
4500b87 
## Fixes issue
#323

## Description of Changes
Added test to cover an empty string timezone being passed.
sea-kelp pushed a commit to OrcaCollective/OpenOversight that referenced this issue Sep 24, 2023
@michplunkett @abandoned-prototype @sea-kelp
Change timestamp datatypes to timestamptz (lucyparsons#978)
2e60d82 
lucyparsons#323

Standardizing column names and changing `timestamp` datatypes to
`timestamptz`. To make sure the times were presented accurately, I
created a function that was called before any other ones to set the
`TIMEZONE` in the session object. From there, I used a filter that
applies the user's timezone to any dates that are presented. This
session object goes away after `60` minutes of no requests, though that
value is configurable should someone to change it.

The solution is essentially based off these two solutions:
- https://stackoverflow.com/a/75271114
- https://stackoverflow.com/a/49891626

Column name changes:
- `descriptions`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `notes`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `raw_images`: `date_image_inserted`: `created_at`, `date_image_taken`:
`taken_at`

As a `timestamp`:
<img width="1097" alt="Screenshot 2023-07-19 at 1 45 05 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/34ead7a9-e2de-43b7-a414-b50b7f0de0cb">

As a `timestamptz`:
<img width="1095" alt="Screenshot 2023-07-19 at 1 52 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/d6a34acb-aa98-43d7-929a-9d80dc01416c">

As a `timestamp` (testing the `downgrade` command):
<img width="1093" alt="Screenshot 2023-07-19 at 1 53 06 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/26c06464-1816-4ca2-8c6d-f5091f79dc9c">

Description and Note `created_at` confirmation:
<img width="524" alt="Screenshot 2023-07-20 at 5 46 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/486e3690-2509-42db-89f2-990a89ea222c">

`descriptions.updated_at`: `2023-07-19 19:08:54.587185 +00:00` -> `July
19, 2023 2:08 PM`
`notes.updated_at`: `2023-07-19 19:06:51.464656 +00:00` -> `July 19,
2023 2:06 PM`

What it will actually look like:
<img width="525" alt="Screenshot 2023-07-20 at 5 55 19 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/5eb6a2d7-975f-4047-827c-1e71d0ff028a">

 - [x] This branch is up-to-date with the `develop` branch.
 - [x] `pytest` passes on my local development environment.
 - [x] `pre-commit` passes on my local development environment.
 - [x] Data-migration output:

<details><summary>Column Name Ouput</summary>

```shell
$ docker exec -it openoversight-web-1 bash
$ flask db stamp head
[2023-07-18 20:22:15,836] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
$ flask db migrate -m "standarize datetime field names"
[2023-07-18 20:27:19,780] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
...
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.taken_at'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_inserted' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_taken' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_taken_at' on '['taken_at']'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_inserted'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_taken'
  Generating /usr/src/app/OpenOversight/migrations/versions/2023-07-18-2027_07ace5f956ca_standarize_datetime_field_names.py ...  done
$ flask db upgrade
[2023-07-18 20:34:18,812] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
$
```
</details>

<details><summary>Datatype Output</summary>

```shell
% docker exec -it openoversight-web-1 bash
$ flask db stamp head
/usr/local/lib/python3.11/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
[2023-07-19 18:49:21,592] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> eb0266dc8588
$ flask db upgrade
[2023-07-19 18:49:46,272] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$ flask db downgrade
[2023-07-19 18:51:59,673] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1931b987ce0d -> 07ace5f956ca, convert timestamp to timestamptz
$ flask db upgrade
[2023-07-19 18:52:28,084] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$
```
</details>

---------

Co-authored-by: abandoned-prototype <[email protected]>
sea-kelp pushed a commit to OrcaCollective/OpenOversight that referenced this issue Sep 24, 2023
@michplunkett @sea-kelp
Add additional /timezone test (lucyparsons#989)
5b6d692 
## Fixes issue
lucyparsons#323

## Description of Changes
Added test to cover an empty string timezone being passed.
sea-kelp pushed a commit to OrcaCollective/OpenOversight that referenced this issue Sep 25, 2023
@michplunkett @abandoned-prototype @sea-kelp
Change timestamp datatypes to timestamptz (lucyparsons#978)
0cd788d 
lucyparsons#323

Standardizing column names and changing `timestamp` datatypes to
`timestamptz`. To make sure the times were presented accurately, I
created a function that was called before any other ones to set the
`TIMEZONE` in the session object. From there, I used a filter that
applies the user's timezone to any dates that are presented. This
session object goes away after `60` minutes of no requests, though that
value is configurable should someone to change it.

The solution is essentially based off these two solutions:
- https://stackoverflow.com/a/75271114
- https://stackoverflow.com/a/49891626

Column name changes:
- `descriptions`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `notes`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `raw_images`: `date_image_inserted`: `created_at`, `date_image_taken`:
`taken_at`

As a `timestamp`:
<img width="1097" alt="Screenshot 2023-07-19 at 1 45 05 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/34ead7a9-e2de-43b7-a414-b50b7f0de0cb">

As a `timestamptz`:
<img width="1095" alt="Screenshot 2023-07-19 at 1 52 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/d6a34acb-aa98-43d7-929a-9d80dc01416c">

As a `timestamp` (testing the `downgrade` command):
<img width="1093" alt="Screenshot 2023-07-19 at 1 53 06 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/26c06464-1816-4ca2-8c6d-f5091f79dc9c">

Description and Note `created_at` confirmation:
<img width="524" alt="Screenshot 2023-07-20 at 5 46 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/486e3690-2509-42db-89f2-990a89ea222c">

`descriptions.updated_at`: `2023-07-19 19:08:54.587185 +00:00` -> `July
19, 2023 2:08 PM`
`notes.updated_at`: `2023-07-19 19:06:51.464656 +00:00` -> `July 19,
2023 2:06 PM`

What it will actually look like:
<img width="525" alt="Screenshot 2023-07-20 at 5 55 19 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/5eb6a2d7-975f-4047-827c-1e71d0ff028a">

 - [x] This branch is up-to-date with the `develop` branch.
 - [x] `pytest` passes on my local development environment.
 - [x] `pre-commit` passes on my local development environment.
 - [x] Data-migration output:

<details><summary>Column Name Ouput</summary>

```shell
$ docker exec -it openoversight-web-1 bash
$ flask db stamp head
[2023-07-18 20:22:15,836] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
$ flask db migrate -m "standarize datetime field names"
[2023-07-18 20:27:19,780] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
...
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.taken_at'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_inserted' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_taken' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_taken_at' on '['taken_at']'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_inserted'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_taken'
  Generating /usr/src/app/OpenOversight/migrations/versions/2023-07-18-2027_07ace5f956ca_standarize_datetime_field_names.py ...  done
$ flask db upgrade
[2023-07-18 20:34:18,812] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
$
```
</details>

<details><summary>Datatype Output</summary>

```shell
% docker exec -it openoversight-web-1 bash
$ flask db stamp head
/usr/local/lib/python3.11/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
[2023-07-19 18:49:21,592] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> eb0266dc8588
$ flask db upgrade
[2023-07-19 18:49:46,272] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$ flask db downgrade
[2023-07-19 18:51:59,673] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1931b987ce0d -> 07ace5f956ca, convert timestamp to timestamptz
$ flask db upgrade
[2023-07-19 18:52:28,084] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$
```
</details>

---------

Co-authored-by: abandoned-prototype <[email protected]>
sea-kelp pushed a commit to OrcaCollective/OpenOversight that referenced this issue Sep 25, 2023
@michplunkett @sea-kelp
Add additional /timezone test (lucyparsons#989)
90f1235 
## Fixes issue
lucyparsons#323

## Description of Changes
Added test to cover an empty string timezone being passed.
AetherUnbound pushed a commit to OrcaCollective/OpenOversight that referenced this issue Oct 9, 2023
@michplunkett @abandoned-prototype @AetherUnbound
Change timestamp datatypes to timestamptz (lucyparsons#978)
c9432df 
lucyparsons#323

Standardizing column names and changing `timestamp` datatypes to
`timestamptz`. To make sure the times were presented accurately, I
created a function that was called before any other ones to set the
`TIMEZONE` in the session object. From there, I used a filter that
applies the user's timezone to any dates that are presented. This
session object goes away after `60` minutes of no requests, though that
value is configurable should someone to change it.

The solution is essentially based off these two solutions:
- https://stackoverflow.com/a/75271114
- https://stackoverflow.com/a/49891626

Column name changes:
- `descriptions`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `notes`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `raw_images`: `date_image_inserted`: `created_at`, `date_image_taken`:
`taken_at`

As a `timestamp`:
<img width="1097" alt="Screenshot 2023-07-19 at 1 45 05 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/34ead7a9-e2de-43b7-a414-b50b7f0de0cb">

As a `timestamptz`:
<img width="1095" alt="Screenshot 2023-07-19 at 1 52 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/d6a34acb-aa98-43d7-929a-9d80dc01416c">

As a `timestamp` (testing the `downgrade` command):
<img width="1093" alt="Screenshot 2023-07-19 at 1 53 06 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/26c06464-1816-4ca2-8c6d-f5091f79dc9c">

Description and Note `created_at` confirmation:
<img width="524" alt="Screenshot 2023-07-20 at 5 46 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/486e3690-2509-42db-89f2-990a89ea222c">

`descriptions.updated_at`: `2023-07-19 19:08:54.587185 +00:00` -> `July
19, 2023 2:08 PM`
`notes.updated_at`: `2023-07-19 19:06:51.464656 +00:00` -> `July 19,
2023 2:06 PM`

What it will actually look like:
<img width="525" alt="Screenshot 2023-07-20 at 5 55 19 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/5eb6a2d7-975f-4047-827c-1e71d0ff028a">

 - [x] This branch is up-to-date with the `develop` branch.
 - [x] `pytest` passes on my local development environment.
 - [x] `pre-commit` passes on my local development environment.
 - [x] Data-migration output:

<details><summary>Column Name Ouput</summary>

```shell
$ docker exec -it openoversight-web-1 bash
$ flask db stamp head
[2023-07-18 20:22:15,836] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
$ flask db migrate -m "standarize datetime field names"
[2023-07-18 20:27:19,780] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
...
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.taken_at'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_inserted' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_taken' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_taken_at' on '['taken_at']'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_inserted'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_taken'
  Generating /usr/src/app/OpenOversight/migrations/versions/2023-07-18-2027_07ace5f956ca_standarize_datetime_field_names.py ...  done
$ flask db upgrade
[2023-07-18 20:34:18,812] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
$
```
</details>

<details><summary>Datatype Output</summary>

```shell
% docker exec -it openoversight-web-1 bash
$ flask db stamp head
/usr/local/lib/python3.11/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
[2023-07-19 18:49:21,592] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> eb0266dc8588
$ flask db upgrade
[2023-07-19 18:49:46,272] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$ flask db downgrade
[2023-07-19 18:51:59,673] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1931b987ce0d -> 07ace5f956ca, convert timestamp to timestamptz
$ flask db upgrade
[2023-07-19 18:52:28,084] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$
```
</details>

---------

Co-authored-by: abandoned-prototype <[email protected]>
AetherUnbound pushed a commit to OrcaCollective/OpenOversight that referenced this issue Oct 9, 2023
@michplunkett @AetherUnbound
Add additional /timezone test (lucyparsons#989)
a6f5497 
## Fixes issue
lucyparsons#323

## Description of Changes
Added test to cover an empty string timezone being passed.
AetherUnbound pushed a commit to OrcaCollective/OpenOversight that referenced this issue Oct 9, 2023
@michplunkett @abandoned-prototype @AetherUnbound
Change timestamp datatypes to timestamptz (lucyparsons#978)
868f032 
lucyparsons#323

Standardizing column names and changing `timestamp` datatypes to
`timestamptz`. To make sure the times were presented accurately, I
created a function that was called before any other ones to set the
`TIMEZONE` in the session object. From there, I used a filter that
applies the user's timezone to any dates that are presented. This
session object goes away after `60` minutes of no requests, though that
value is configurable should someone to change it.

The solution is essentially based off these two solutions:
- https://stackoverflow.com/a/75271114
- https://stackoverflow.com/a/49891626

Column name changes:
- `descriptions`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `notes`: `date_created` -> `created_at`, `date_updated` ->
`updated_at`
- `raw_images`: `date_image_inserted`: `created_at`, `date_image_taken`:
`taken_at`

As a `timestamp`:
<img width="1097" alt="Screenshot 2023-07-19 at 1 45 05 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/34ead7a9-e2de-43b7-a414-b50b7f0de0cb">

As a `timestamptz`:
<img width="1095" alt="Screenshot 2023-07-19 at 1 52 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/d6a34acb-aa98-43d7-929a-9d80dc01416c">

As a `timestamp` (testing the `downgrade` command):
<img width="1093" alt="Screenshot 2023-07-19 at 1 53 06 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/26c06464-1816-4ca2-8c6d-f5091f79dc9c">

Description and Note `created_at` confirmation:
<img width="524" alt="Screenshot 2023-07-20 at 5 46 46 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/486e3690-2509-42db-89f2-990a89ea222c">

`descriptions.updated_at`: `2023-07-19 19:08:54.587185 +00:00` -> `July
19, 2023 2:08 PM`
`notes.updated_at`: `2023-07-19 19:06:51.464656 +00:00` -> `July 19,
2023 2:06 PM`

What it will actually look like:
<img width="525" alt="Screenshot 2023-07-20 at 5 55 19 PM"
src="https://github.com/lucyparsons/OpenOversight/assets/5885605/5eb6a2d7-975f-4047-827c-1e71d0ff028a">

 - [x] This branch is up-to-date with the `develop` branch.
 - [x] `pytest` passes on my local development environment.
 - [x] `pre-commit` passes on my local development environment.
 - [x] Data-migration output:

<details><summary>Column Name Ouput</summary>

```shell
$ docker exec -it openoversight-web-1 bash
$ flask db stamp head
[2023-07-18 20:22:15,836] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
$ flask db migrate -m "standarize datetime field names"
[2023-07-18 20:27:19,780] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
...
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'descriptions.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'descriptions.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'notes.updated_at'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_updated'
INFO  [alembic.autogenerate.compare] Detected removed column 'notes.date_created'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'raw_images.taken_at'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_inserted' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected removed index 'ix_raw_images_date_image_taken' on 'raw_images'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_raw_images_taken_at' on '['taken_at']'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_inserted'
INFO  [alembic.autogenerate.compare] Detected removed column 'raw_images.date_image_taken'
  Generating /usr/src/app/OpenOversight/migrations/versions/2023-07-18-2027_07ace5f956ca_standarize_datetime_field_names.py ...  done
$ flask db upgrade
[2023-07-18 20:34:18,812] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
$
```
</details>

<details><summary>Datatype Output</summary>

```shell
% docker exec -it openoversight-web-1 bash
$ flask db stamp head
/usr/local/lib/python3.11/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
[2023-07-19 18:49:21,592] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> eb0266dc8588
$ flask db upgrade
[2023-07-19 18:49:46,272] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade eb0266dc8588 -> 07ace5f956ca, standardize datetime field names
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$ flask db downgrade
[2023-07-19 18:51:59,673] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 1931b987ce0d -> 07ace5f956ca, convert timestamp to timestamptz
$ flask db upgrade
[2023-07-19 18:52:28,084] INFO in __init__: OpenOversight startup
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 07ace5f956ca -> 1931b987ce0d, convert timestamp to timestamptz
$
```
</details>

---------

Co-authored-by: abandoned-prototype <[email protected]>
AetherUnbound pushed a commit to OrcaCollective/OpenOversight that referenced this issue Oct 9, 2023
@michplunkett @AetherUnbound
Add additional /timezone test (lucyparsons#989)
95baeb1 
## Fixes issue
lucyparsons#323

## Description of Changes
Added test to cover an empty string timezone being passed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@michplunkett michplunkett

Labels
feature-request size/M tech/python
Projects
None yet
Milestone
Backlog
4 participants
@r4v5 @michplunkett @redshiftzero @abandoned-prototype