Press "Enter" to skip to content

Category: Dates and Numbers

Removing Leading Zeroes from a String in T-SQL

Published 2024-07-12 by Kevin Feasel

Steve Stedman gets rid of leading zeroes:

When working with data in SQL Server, there may be times when you need to remove leading zeros from a string. This task can be particularly common when dealing with numerical data stored as strings, such as ZIP codes, product codes, or other formatted numbers. In this blog post, we’ll explore several methods to remove leading zeros in SQL Server.

I’m not sure I see the reason to use anything other than CAST() (or, better yet, TRY_CAST()), but Steve does show two other methods.

2 Comments

Random Date Generation in Python

Published 2024-06-28 by Kevin Feasel

Chris LaGreca spits out some dates:

I often work with time series data and find it useful to have a variety of ways to randomly generate dates. This particular example is great for evenly distributed date partitions. Running the script below with the default arguments will output a list of random dates, one for each month of the year.

It looks like this is generating based off of a uniform distribution, which probably makes the most sense for “give me a day of the month” data generation.

Comments closed

An Introduction to pg_timeseries

Published 2024-05-22 by Kevin Feasel

Samay Sharma and Jason Petersen have an announcement:

We are excited to launch pg_timeseries: a PostgreSQL extension focused on creating a cohesive user experience around the creation, maintenance, and use of time-series tables. You can now use pg_timeseries to create time-series tables, configure the compression and retention of older data, monitor time-series partitions, and run complex time-series analytics functions with a user-friendly syntax. pg_timeseries is open-sourced under the PostgreSQL license and can be added to your existing PostgreSQL installation or tried as a part of the Timeseries Stack on Tembo Cloud.

Read on to learn more about how it works. The syntax and concepts do remind me a good bit of InfluxDB, as well.

Comments closed

Arbitrary Intervals for Partitioning in Postgres

Published 2024-05-10 by Kevin Feasel

Keith Fiske does a bit of interval math:

Whether you are managing a large table or setting up automatic archiving, time based partitioning in Postgres is incredibly powerful. pg_partman’s newest versions support a huge variety of custom time internals. Marco just published a post on using pg_partman with our new database product for doing analytics with Postgres, Crunchy Bridge for Analytics. So I thought this would be a great time to review the basic and complex options for the time based partitioning.

Read on for a note of how pg_partman works and interval management, especially for versions earlier than 5.0.

Comments closed

Adding the Current Date and Time to a PySpark Data Frame

Published 2024-05-09 by Kevin Feasel

Gilbert Quevauvilliers wants to know what time it is:

How to add current DateTime to existing PySpark data frame in a Fabric Notebook

In the blog post below, I am going to describe how to add the current Date Time to your existing Spark data frame.

This is really useful when I am inserting data into a Fabric Lakehouse table, and I want to know when the data got inserted.

Read on for the answer.

Comments closed

Migrating DATETIME Data to DATETIMEOFFSET

Published 2024-05-06 by Kevin Feasel

William Assaf adds some time zones:

I recently reviewed, worked on, and added a similar example to the DATETIMEOFFSET Microsoft Learn Docs article at the recommendation of my colleague Randolph West, who guessed (accurately) I would enjoy such a task. It was a nice pre-Build diversion. 

This topic is one that I have co-presented on in the past and hounded project capstone review presentations about. If you’re not storing time zone offset in your date/time data, you’re setting yourself up for future pain. That future pain is not what this blog post is about.

My preference is not to store time zone offset but instead store everything in UTC and perform any time zone switcharoos in the UI. But if you are storing local dates and times, I completely agree that you should keep track of the time zone. I worked for an east coast US company that bought a west coast US company, and both stored local dates and times in their SQL Server databases, making data consolidation a real challenge.

Comments closed

Working with Date Sequences in R

Published 2024-02-15 by Kevin Feasel

Steven Sanderson isn’t satisfied with a single date:

In the world of data analysis and manipulation, working with dates is a common and crucial task. Whether you’re analyzing financial data, tracking trends over time, or forecasting future events, understanding how to generate date sequences efficiently is essential. In this blog post, we’ll explore three powerful R packages—lubridate, timetk, and base R—that make working with dates a breeze. By the end of this guide, you’ll be equipped with the knowledge to generate date sequences effortlessly and efficiently in R.

Click through for several ways to generate date sequences, including weekly sequences.

Comments closed

Checking for Date Columns in R

Published 2024-02-12 by Kevin Feasel

Steven Sanderson is looking for a date:

As an R programmer, you may often encounter datasets where you need to determine whether a column contains date values. This task is crucial for data cleaning, manipulation, and analysis. In this blog post, we’ll explore various methods to check if a column is a date in R, with a focus on using the lubridate package and the ts_is_date_class() function from the healthyR.ts package.

Click through to see how, using lubridate and healthyR.

Comments closed

Finding the Week Number in R

Published 2024-02-08 by Kevin Feasel

Steven Sanderson checks the week:

When working with dates in R, you may need to extract the week number for any given date. This can be useful for doing time series analysis or visualizations by week.

In this post, I’ll demonstrate how to get the week number from dates in R using both base R and the lubridate package. I’ll provide simple examples so you can try it yourself.

Steven also makes a good point about ISO weeks (which are common in Europe) versus calendar weeks.

Comments closed

Translating Excel Date Values into R Dates

Published 2024-02-07 by Kevin Feasel

Steven Sanderson reads an Excel file:

Have you ever battled with Excel’s quirky date formats in your R projects? If so, you’re not alone! Those cryptic numbers can be a real headache, but fear not, fellow R warriors! Today, we’ll conquer this challenge and transform those numbers into beautiful, usable dates.

This is a common pain point in a lot of libraries and Steven shows how to solve it in R using a pair of functions.

Comments closed