登录查看更多内容

What are the best ways to handle dates and times in data manipulation?

由人工智能和领英社区提供技术支持

Dates and times are essential components of many data sets, but they can also pose significant challenges for data manipulation. Different formats, time zones, daylight saving rules, and calendar systems can make it difficult to compare, aggregate, filter, or transform date and time values. In this article, you will learn some of the best ways to handle dates and times in data manipulation, using common tools and frameworks for data engineering.

此文章中的业界达人

由社区从 10 条内容中精选。了解更多

POOJA JAIN

Storyteller | Linkedin Top Voice 2024 | Senior Data Engineer@ Globant | Linkedin Learning Instructor | 2xGCP & AWS…
Tomas Peluritis

Data Person | Data Engineering, Technical Leadership | Uncle Data
Rahul Nanavaty

2x LinkedIn Top Data Engineering Voice??Director, Data Architecture and Engineering ??5X Snowflake GCP AWS Certified ??…

1 Understand the data source

The first step to handle dates and times in data manipulation is to understand the data source and how it represents date and time values. Some data sources may use a standard format, such as ISO 8601, which specifies the year, month, day, hour, minute, second, and optional time zone in a consistent and unambiguous way. Other data sources may use a custom or regional format, such as MM/DD/YYYY or DD/MM/YYYY, which can cause confusion or errors if not properly parsed. You should always check the documentation or metadata of the data source to find out the format and meaning of the date and time values, and use appropriate tools or libraries to parse them correctly.

添加您的观点

Rahul Nanavaty

2x LinkedIn Top Data Engineering Voice??Director, Data Architecture and Engineering ??5X Snowflake GCP AWS Certified ?? Presales Expert?? Data Observability ??AI and AI Governance??
举报内容
In our organization, we standardized timestamp handling in data manipulation: 1. Consistent Formats: All dates/timestamp follow ISO 8601 YYYY-MM-DD hh-mm-ss 2. Time Zones: Explicit time zones prevent ambiguity 3. Date Libraries: We use language-specific libraries for operations 4. Data Validation: Rigorous validation ensures data quality 5. Naming Conventions: Standardized names clarify date columns 6. Database Functions: SQL queries use database date functions 7. Transformation Pipelines: Pipelines include date steps 8. Documentation: Comprehensive guides aid understanding 9. Continuous Improvement: Regular reviews adapt to evolving needs. These practices enhance data quality, enable precise analysis, and streamline data manipulation.

已翻译

赞
Gytis Repe?ka

Solution Architect ?? Data and Information Systems ??
举报内容
Different source systems will feed various date and time formats. The most confusing is always american Month-Day-Year. In my practise, I always cast format to YYYY-MM-DD store it in appropriate datatype. Then formatting for display purposes is easy depending on requirements. Timestamps may be easier as HH:MM:SS is quite common, however, some systems require higher seconds precision - that should be planned in advance (if source system provides that part).

已翻译

赞

2 Choose a common representation

The second step to handle dates and times in data manipulation is to choose a common representation for the date and time values that you want to manipulate. Depending on the purpose and scope of your data manipulation, you may need to convert the date and time values to a uniform format, such as UTC (Coordinated Universal Time), which is a standard time zone that does not depend on the location or daylight saving rules of the data source. Alternatively, you may need to preserve the original time zone or local time of the data source, but use a consistent notation, such as the offset from UTC (e.g., +02:00 or -05:00), to indicate the difference. You should also decide whether you need to store or manipulate the date and time values as strings, numbers, or objects, and use appropriate data types and formats for your chosen representation.

添加您的观点

Tomas Peluritis

Data Person | Data Engineering, Technical Leadership | Uncle Data
举报内容
My go-to solution is always to choose UTC. At least where I'm from, it's common to have summer/winter times, and a fun thing always happens when there is a switch - either you skip a run or trigger it twice. That happens if your flows are scheduled when the shift happens. Another one - always choose the same format for date and datetime representation. Less confusion, and everyone is on the same page. Personally, YYYY-MM-DD is the best date; others are confusing.

已翻译

赞
Gytis Repe?ka

Solution Architect ?? Data and Information Systems ??
举报内容
UTC all the way ?? And afterwards format it to any time zone required for output - easy with internal database functions, which can even display timezone in desired format: Europe/Vilnius, EET or +200 - any way you like.

已翻译

赞

3 Use built-in or external functions

The third step to handle dates and times in data manipulation is to use built-in or external functions that can perform common operations on date and time values, such as extracting, adding, subtracting, comparing, or formatting. Most data manipulation tools and frameworks, such as SQL, Python, Pandas, Spark, or R, have built-in functions or libraries that can handle date and time values in various ways. For example, you can use the DATEPART, DATEADD, DATEDIFF, or FORMAT functions in SQL to manipulate date and time values in a relational database. You can also use external libraries or packages, such as dateutil, arrow, or pendulum in Python, or lubridate or anytime in R, to extend the functionality and usability of date and time manipulation.

添加您的观点

Tomas Peluritis

Data Person | Data Engineering, Technical Leadership | Uncle Data
举报内容
Always try to use built-in functions, and if possible, try to use existing ones in SQL. Even though there are differences in dialects, it's less of a hustle to move from one to another than to re-write a whole method. As usual, if you have a standard - keep to it; don't invent a wheel with something new; if you don't - create one. So many headaches will be solved by doing it.

已翻译

赞
Gytis Repe?ka

Solution Architect ?? Data and Information Systems ??
举报内容
Calendar functions are essential to get precise timeslots, e.g.: when counting number of days, weeks, minutes, etc. Some financial algorithms require to use 365 as number of days in year, but unless explicitly required, all other date calculations should use internal date calculation functions.

已翻译

赞

4 Handle edge cases and anomalies

The fourth step to handle dates and times in data manipulation is to handle edge cases and anomalies that may arise from the complexity and variability of date and time values. These can include missing or invalid values, such as NULL, 00/00/0000, or 29/02/2021, which should be validated and cleaned before manipulating them. Additionally, leap years and leap seconds must be accounted for with functions or libraries that can handle them correctly. Daylight saving time (DST) may also cause ambiguity or inconsistency in date and time values, so the standard or local time zone of the data source should be used. Functions or libraries should also be used to handle DST transitions correctly. By taking these steps, you can ensure that your data manipulation is accurate and reliable.

添加您的观点

Tomas Peluritis

Data Person | Data Engineering, Technical Leadership | Uncle Data
举报内容
What works is using the UNIX timestamp min value as min value date if a data had a not null constraint; it's 1970-01-01, though if it's birthdays, I'd go with nulls. For the future dates, I've seen examples of 9999-12-31, etc., but it might be overkill (not all DBs are supporting that far in the future), so use a lower date, i.e. 3000-12-31 or even 2099-12-31 will be enough. Most likely, your system won't live that long anyway.

已翻译

赞
Gytis Repe?ka

Solution Architect ?? Data and Information Systems ??
举报内容
When it comes to unknown start and end dates, I pefer using NULL values, since UNIX start date is definitive and really not old one: January 1st, 1970 00:00:00 UTC. Some use 9999-12-31, some 2999-12-31 or others as high date, but in fact NULL represents unknown value best.

已翻译

赞

5 Test and document your results

The fifth step to handle dates and times in data manipulation is to test and document your results. You should always verify that your date and time manipulation has produced the expected and accurate results, and that you have not introduced any errors or biases in your data. You can use tools or methods such as unit testing, data profiling, or data visualization to check and validate your results. You should also document your date and time manipulation process and logic, and explain the assumptions, choices, and limitations that you have made. You can use tools or methods such as code comments, documentation, or metadata to record and communicate your date and time manipulation.

添加您的观点

6 Learn from best practices and resources

The sixth and final step to handle dates and times in data manipulation is to learn from best practices and resources that can help you improve your skills and knowledge in this area. It is important to follow standards and conventions, such as ISO 8601, RFC 3339, or POSIX. Additionally, use the appropriate tools and libraries for your data manipulation environment and language, and make sure they are up-to-date and compatible. Furthermore, consult the documentation and examples of the data sources, tools, and libraries that you use, as well as seek advice from other data engineers or experts who have experience with date and time manipulation. Finally, read books, articles, blogs, or podcasts that cover date and time manipulation topics to gain further knowledge.

添加您的观点

POOJA JAIN

Storyteller | Linkedin Top Voice 2024 | Senior Data Engineer@ Globant | Linkedin Learning Instructor | 2xGCP & AWS Certified | LICAP'2022
举报内容
Few of the common best practices to handle dates and time manipulation includes - - Use a consistent date and time format. - Use a date and time library. - Ensure to have clarity on time zones - Use appropriate date and time data types - Compare date/time before converting to ensure consistency There could be other best practices to follow test cases, and validate the data before proceeding. Additionally, ensure to handle errors gracefully, test your code well and document it properly, adding appropriate data types.

已翻译

赞
Gytis Repe?ka

Solution Architect ?? Data and Information Systems ??
举报内容
Once you've chosen the way dates and timestamps are supposed to be stored - commit to that and don't change ever. Format according to needs for usage, but keep storage format consistent and fixed.

已翻译

赞

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Data Engineering

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the best ways to handle dates and times in data manipulation?

1

2

3

4

5

6

7

1 Understand the data source

2 Choose a common representation

3 Use built-in or external functions

4 Handle edge cases and anomalies

5 Test and document your results

6 Learn from best practices and resources

7 Here’s what else to consider

Data Engineering

给文章评分

感谢您的反馈

更多Data Engineering相关文章

更多相关阅读内容

What are the best ways to handle dates and times in data manipulation?

1

2

3

4

5

6

7

1 Understand the data source

2 Choose a common representation

3 Use built-in or external functions

4 Handle edge cases and anomalies

5 Test and document your results

6 Learn from best practices and resources

7 Here’s what else to consider

Data Engineering

给文章评分

感谢您的反馈

查看其他技能