I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger — each time a record is created or updated. All the tables/triggers use the same db sequence. Each time a record is updated anywhere in the db, that one sequence is used to get the next available integer. (I suppose I could consider the sequence to be a sort of global sequence).
IBM: Usage of ROWSTAMP column in Maximo tables
It’s unfortunate that those ROWSTAMP columns are integers, and not dates. What I really need in the tables are CHANGEDATE columns. Dates would be useful for data entry troubleshooting, reporting, and analysis in general. But I can’t really justify customizing hundreds of tables and adding hundreds of triggers in the OOB system. The customized tables wouldn’t be supported by IBM and it wouldn’t be worth the effort/added complexity.
I feel like those ROWSTAMP columns are so close to being useful to me. I can’t help but wonder:
Is there a way to retrieve the date that those ROWSTAMP sequence values were generated?
For example, and I doubt this is possible, could I add a DATE column to the sequence, and then join from my table to the sequence to get the date? Or could I use some sort of logging mechanism on the sequence to put rows (with dates) in a table whenever the next sequence value is used?
I have post_type called ‘series’ and posts with sharing category as attachment. The problem is when I create list using post_type by date, the date follows post_type post date and not latest ‘post’. How can I change that automatically?
I want to update date column of the table with dates in the increment of 1 for all rows.
How do I do it in MySQL?
Table: tbl_question_of_the_day Column: date
Why are you selling this site?
Need to work on different projects
How is it monetized?
PropellerAds and revenuehits
Does this site come with any social media accounts?
How much time does this site take to run?
no need to work. Its getting organic traffic
What challenges are there with running this site?
Keep updating the contents
I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly)
For example if the
Startdate is 2015/10/02 and the
Enddate is 2015/12/22
and my date range is Monthly (M)
then my required output is:
Output ------------- Newstartdate newEnddate 2015/10/02. 2015/10/31 2015/11/01. 2015/11/30 2015/12/01. 2015/12/30
So I was looking for a generic PL/SQL query to split data what ever date range can be(Y/Q/M/W/D) based on the start and end dates and the range specification.
I want to apply a location-based time zone to posts before a certain date. These posts have been converted from a non-Wordpress database and the datetimes are now displayed with a time offset. Can you help me with that? Thanks in advance for the answers!
I have a query that insert only data in table stock by date, now i want to do a function that also have update.
See fiddle : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f380003bb7481b37928da5cea669ea0a
Is it possible to use the advanced filter to select a specific future date, dynamically? For example if I want to return all data with a "date" field populated for 30 days from today, but no data from before or after that? From what I am seeing the only filter option similar is "Next X Days" but it’s inclusive of all days to that point.
According to Kimball:
A dimension can contain a reference to another dimension table. For instance, a bank account dimension can reference a separate dimension representing the date the account was opened.
This suggests to me you’d be able to just query the Account dimension directly if you wanted to track number of accounts opened over time, for example. Is this common?
In my case, I’m modeling Customer as a dimension, which also references Date (for Signed Up Date) and Demographic dimensions.
A common kind of query the business ask is how many signups we’ve had over time by demographic, for example. This doesn’t involve a fact table at all. All this can be worked out by summarizing the dimension itself. Does this indicate some kind of a smell in the schema design, or is this just a trait of certain kinds of dimensions where the dimension itself has some significance to the business?
I have a table that dictates who was on a project, from a startdate to an enddate. I’d like some help writing a query that will return the number of "active" users at the end of every month, for the past 12 months.
CREATE TABLE `roster` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `project_id` int(11) DEFAULT NULL, `start_date` datetime NOT NULL, `end_date` datetime NOT NULL, `closed_date` datetime DEFAULT NULL, PRIMARY KEY (`id`) );
id user_id project_id start_date end_date closed_date 1 1 1 2019-05-27 00:00:00 2021-02-01 00:00:00 NULL 2 2 2 2020-05-27 00:00:00 2021-02-01 00:00:00 2020-02-05 00:00:00 3 3 3 2020-05-27 00:00:00 2024-02-01 00:00:00 2020-02-05 00:00:00
And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).
So for the dataset above, we can see that all 3 projects were "active" in the month January 2021, because the end date is in the future.
2020-01-31 | 3
The last bit of complexity is that sometimes a project can be closed before the EndDate and I’d like to exclude any users who’s endDate is in the future but the project has actually closed.
For example in the dataset above, the third project has a end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March.
p.s Hoping to get an answer in Postgres (I’m using Redshift)