Should I split timestamp parts into separate columns?Should we add extra 5 columns or build a separate table?Should I separate frequently updated columns?Is it worth to separate columns into multiple tables for one-to-one relational tableAre triggers bad for created/timestamp columns?Split two rows into two columnsShould I split this large table into three smaller tables?Should I move repeating foreign keys into separate table?Extracting 'hot columns' into a separate tableTransfer microsecond timestamp into table using COPYDatabase performance improvements for current setup. (mysql - marriaDB)

Multi tool use
Multi tool use

Why is only the fundamental frequency component said to give useful power?

Overlapping String-Blocks

Source that a married woman seduced by a “messianic figure” is still permitted to her husband

Are there any important biographies of nobodies?

How to construct an hbox with negative height?

Watts vs. volts amperes

Winning Strategy for the Magician and his Apprentice

Do simulator games use a realistic trajectory to get into orbit?

How is water heavier than petrol, even though its molecular weight is less than petrol?

An average heaven where everyone has sexless golden bodies and is bored

Thread Pool C++ Implementation

Is open-sourcing the code of a webapp not recommended?

Compiling c files on ubuntu and using the executable on Windows

Grover algorithm for a database search: where is the quantum advantage?

Passing multiple files through stdin (over ssh)

What is the `some` keyword in SwiftUI?

How can electric fields be used to detect cracks in metals?

SQL counting distinct over partition

Why did the Herschel Space Telescope need helium coolant?

Can I make plugins required?

Recommended tools for graphs and charts

Are there any instruments that don't produce overtones?

What is wrong with this proof that symmetric matrices commute?

Logarithm of exponential



Should I split timestamp parts into separate columns?


Should we add extra 5 columns or build a separate table?Should I separate frequently updated columns?Is it worth to separate columns into multiple tables for one-to-one relational tableAre triggers bad for created/timestamp columns?Split two rows into two columnsShould I split this large table into three smaller tables?Should I move repeating foreign keys into separate table?Extracting 'hot columns' into a separate tableTransfer microsecond timestamp into table using COPYDatabase performance improvements for current setup. (mysql - marriaDB)






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.



At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.



id | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 | 4 | 13 | 15 | 0 | 19


vs


id
-------------------------
Fri Apr 13 2018 15:00:19


My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:



SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/


I would appreciate some best practices input on this.










share|improve this question
























  • Add fields you need and update them in triggers.

    – Akina
    May 21 at 10:06











  • Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

    – Jon of All Trades
    May 21 at 15:37











  • @JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

    – Khabz
    May 22 at 9:41

















2















I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.



At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.



id | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 | 4 | 13 | 15 | 0 | 19


vs


id
-------------------------
Fri Apr 13 2018 15:00:19


My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:



SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/


I would appreciate some best practices input on this.










share|improve this question
























  • Add fields you need and update them in triggers.

    – Akina
    May 21 at 10:06











  • Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

    – Jon of All Trades
    May 21 at 15:37











  • @JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

    – Khabz
    May 22 at 9:41













2












2








2








I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.



At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.



id | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 | 4 | 13 | 15 | 0 | 19


vs


id
-------------------------
Fri Apr 13 2018 15:00:19


My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:



SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/


I would appreciate some best practices input on this.










share|improve this question
















I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.



At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.



id | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 | 4 | 13 | 15 | 0 | 19


vs


id
-------------------------
Fri Apr 13 2018 15:00:19


My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:



SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/


I would appreciate some best practices input on this.







postgresql database-design query-performance optimization timestamp






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 21 at 20:07









MDCCL

6,99831847




6,99831847










asked May 21 at 9:59









KhabzKhabz

1113




1113












  • Add fields you need and update them in triggers.

    – Akina
    May 21 at 10:06











  • Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

    – Jon of All Trades
    May 21 at 15:37











  • @JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

    – Khabz
    May 22 at 9:41

















  • Add fields you need and update them in triggers.

    – Akina
    May 21 at 10:06











  • Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

    – Jon of All Trades
    May 21 at 15:37











  • @JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

    – Khabz
    May 22 at 9:41
















Add fields you need and update them in triggers.

– Akina
May 21 at 10:06





Add fields you need and update them in triggers.

– Akina
May 21 at 10:06













Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

– Jon of All Trades
May 21 at 15:37





Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.

– Jon of All Trades
May 21 at 15:37













@JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

– Khabz
May 22 at 9:41





@JonofAllTrades The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps

– Khabz
May 22 at 9:41










2 Answers
2






active

oldest

votes


















6














Keep the timestamp and don't add columns for the parts.



If you need to search for part of a timestamp, you can always create indexes on extract expressions.



Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.






share|improve this answer























  • In that case should I have a separate table or have the timestamp object inside each table?

    – Khabz
    May 21 at 12:20












  • Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

    – Khabz
    May 21 at 12:30






  • 2





    Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

    – Laurenz Albe
    May 21 at 13:52


















5














You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.



When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).



Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:



  • Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.

  • Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.





share|improve this answer























    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238669%2fshould-i-split-timestamp-parts-into-separate-columns%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    6














    Keep the timestamp and don't add columns for the parts.



    If you need to search for part of a timestamp, you can always create indexes on extract expressions.



    Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.






    share|improve this answer























    • In that case should I have a separate table or have the timestamp object inside each table?

      – Khabz
      May 21 at 12:20












    • Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

      – Khabz
      May 21 at 12:30






    • 2





      Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

      – Laurenz Albe
      May 21 at 13:52















    6














    Keep the timestamp and don't add columns for the parts.



    If you need to search for part of a timestamp, you can always create indexes on extract expressions.



    Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.






    share|improve this answer























    • In that case should I have a separate table or have the timestamp object inside each table?

      – Khabz
      May 21 at 12:20












    • Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

      – Khabz
      May 21 at 12:30






    • 2





      Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

      – Laurenz Albe
      May 21 at 13:52













    6












    6








    6







    Keep the timestamp and don't add columns for the parts.



    If you need to search for part of a timestamp, you can always create indexes on extract expressions.



    Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.






    share|improve this answer













    Keep the timestamp and don't add columns for the parts.



    If you need to search for part of a timestamp, you can always create indexes on extract expressions.



    Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered May 21 at 11:03









    Laurenz AlbeLaurenz Albe

    71014




    71014












    • In that case should I have a separate table or have the timestamp object inside each table?

      – Khabz
      May 21 at 12:20












    • Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

      – Khabz
      May 21 at 12:30






    • 2





      Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

      – Laurenz Albe
      May 21 at 13:52

















    • In that case should I have a separate table or have the timestamp object inside each table?

      – Khabz
      May 21 at 12:20












    • Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

      – Khabz
      May 21 at 12:30






    • 2





      Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

      – Laurenz Albe
      May 21 at 13:52
















    In that case should I have a separate table or have the timestamp object inside each table?

    – Khabz
    May 21 at 12:20






    In that case should I have a separate table or have the timestamp object inside each table?

    – Khabz
    May 21 at 12:20














    Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

    – Khabz
    May 21 at 12:30





    Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?

    – Khabz
    May 21 at 12:30




    2




    2





    Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

    – Laurenz Albe
    May 21 at 13:52





    Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately.

    – Laurenz Albe
    May 21 at 13:52













    5














    You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.



    When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).



    Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:



    • Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.

    • Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.





    share|improve this answer



























      5














      You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.



      When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).



      Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:



      • Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.

      • Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.





      share|improve this answer

























        5












        5








        5







        You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.



        When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).



        Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:



        • Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.

        • Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.





        share|improve this answer













        You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.



        When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).



        Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:



        • Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.

        • Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered May 21 at 15:24









        mustacciomustaccio

        10.7k72343




        10.7k72343



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238669%2fshould-i-split-timestamp-parts-into-separate-columns%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            6v7iRNjQcWNIqAx3bqoijUhSYAy vTn9MgbKvE,SvLd0twP9 gOY1tBLzU,cu Uz h11f,8sehWQGEJSeMzDI,U5IXw
            KBsT I9ehQg,qyYLlQpRkCokP1k

            Popular posts from this blog

            RemoteApp sporadic failureWindows 2008 RemoteAPP client disconnects within a matter of minutesWhat is the minimum version of RDP supported by Server 2012 RDS?How to configure a Remoteapp server to increase stabilityMicrosoft RemoteApp Active SessionRDWeb TS connection broken for some users post RemoteApp certificate changeRemote Desktop Licensing, RemoteAPPRDS 2012 R2 some users are not able to logon after changed date and time on Connection BrokersWhat happens during Remote Desktop logon, and is there any logging?After installing RDS on WinServer 2016 I still can only connect with two users?RD Connection via RDGW to Session host is not connecting

            Vilaño, A Laracha Índice Patrimonio | Lugares e parroquias | Véxase tamén | Menú de navegación43°14′52″N 8°36′03″O / 43.24775, -8.60070

            Cegueira Índice Epidemioloxía | Deficiencia visual | Tipos de cegueira | Principais causas de cegueira | Tratamento | Técnicas de adaptación e axudas | Vida dos cegos | Primeiros auxilios | Crenzas respecto das persoas cegas | Crenzas das persoas cegas | O neno deficiente visual | Aspectos psicolóxicos da cegueira | Notas | Véxase tamén | Menú de navegación54.054.154.436928256blindnessDicionario da Real Academia GalegaPortal das Palabras"International Standards: Visual Standards — Aspects and Ranges of Vision Loss with Emphasis on Population Surveys.""Visual impairment and blindness""Presentan un plan para previr a cegueira"o orixinalACCDV Associació Catalana de Cecs i Disminuïts Visuals - PMFTrachoma"Effect of gene therapy on visual function in Leber's congenital amaurosis"1844137110.1056/NEJMoa0802268Cans guía - os mellores amigos dos cegosArquivadoEscola de cans guía para cegos en Mortágua, PortugalArquivado"Tecnología para ciegos y deficientes visuales. Recopilación de recursos gratuitos en la Red""Colorino""‘COL.diesis’, escuchar los sonidos del color""COL.diesis: Transforming Colour into Melody and Implementing the Result in a Colour Sensor Device"o orixinal"Sistema de desarrollo de sinestesia color-sonido para invidentes utilizando un protocolo de audio""Enseñanza táctil - geometría y color. Juegos didácticos para niños ciegos y videntes""Sistema Constanz"L'ocupació laboral dels cecs a l'Estat espanyol està pràcticament equiparada a la de les persones amb visió, entrevista amb Pedro ZuritaONCE (Organización Nacional de Cegos de España)Prevención da cegueiraDescrición de deficiencias visuais (Disc@pnet)Braillín, un boneco atractivo para calquera neno, con ou sen discapacidade, que permite familiarizarse co sistema de escritura e lectura brailleAxudas Técnicas36838ID00897494007150-90057129528256DOID:1432HP:0000618D001766C10.597.751.941.162C97109C0155020