ensure consistent date, handling and storage in database

[rburghol]

Overview

• dH has function to insure timestamp are handled consistently:
  • code: https://github.com/HARPgroup/dH/blob/26261b10d00ae0fe1ddc520a773b2fb2998a7d5f/dh.module#L2174
  • Uses php.INI setting for time zone if the input does not contain it
• postgres handles basic dated/times
  • has time zone configured in postgresql.conf
  • make_time ( hour int, min int, sec double precision ) → time https://www.postgresql.org/docs/14/functions-datetime.html

Code

Postgresql date

• Input an hour with date: select extract(epoch from to_timestamp('2023-01-01 01:00:00','YYYY-MM-DD HH:MI:SS'))::int;
  • 1672552800

ssh dbase2
sudo fgrep timezone /data/postgres/14/postgresql.conf
timezone = 'America/New_York'

psql -h dbase2 drupal.dh03
select extract(epoch from to_timestamp('2023-01-01','YYYY-MM-DD'))::int;

  extract
------------
 1672549200
(1 row)

PHP dh_handletimestamp

• /etc/php/x.xx/php.ini config
• Ex: fgrep timezone /etc/php/7.4/apache2/php.ini
  • date.timezone = America/New_York
• Date with hour, min, sec: echo dh_handletimestamp("2023-01-01 01:00:00");
  • returns: 1672552800

# enter PHP in terminal mode
php -a

function dh_handletimestamp($ts) {
  // don't do date_received as this is a field and is handled there
  if ( ($ts <> '') and !($ts == NULL)) {
    $orig = $ts;
    // if a valid unix epoch style timestamp has been submitted 
    // this next will try to convert a string
    if (!( !is_numeric($ts) ? (ctype_digit($ts)) : true ) ) {
      // not a valid unix timestamp, so try to convert from some date format
      $ts = strtotime($ts);
      //drupal_set_message("Converted $orig to Epoch: " . $ts);
    } else {
      // valid unix timestamp submitted, proceed without changing
    }
  }
  return $ts;
}

echo dh_handletimestamp("2023-01-01");

# output: 1672549200

Bash date command

• may need us to give it timezone, as it differs in output
• date -d '2023-01-01' +'%s'
  • yields: 1672531200
• Gving a hour, mon sec:
  • TZ="America/New_York" date -d '2023-01-01 01:00:00' +'%s'
  • yields: 1672552800
• We can povide a timezone context to the command which is the most direct way:
  • TZ="America/New_York" date -d '2023-01-01' +'%s'
  • gives:1672549200
  • which matches both postgresql and php
• Per https://linuxize.com/post/how-to-set-or-change-timezone-on-ubuntu-20-04/
  • /etc/localtime is a symbolic link to the desired timezone,
  • default is /etc/localtime -> /usr/share/zoneinfo/Etc/UTC
  • Unfortunately, there is nothing matching New_York, which since it uses DST could need to switch, so adding the TZ option could be best.

date -d '2023-01-01' +'%s'
1672531200
rob@deq2:~$ TZ="America/New_York" date -d '2023-01-01 01:00:00' +'%s'
1672552800
rob@deq2:~$ TZ="America/New_York" date -d '2023-01-01' +'%s'
1672549200
rob@deq2:~$ TZ="" date -d '2023-01-01' +'%s'
1672531200

NLDAS2 Dates

rob@deq2:/opt/model/model_meteorology$ date -d '2023-01-01 16:00:00' +%s
1672588800
rob@deq2:/opt/model/model_meteorology$ date -d '2023-01-01 17:00:00' +%s
1672592400

gdalinfo /backup/meteorology/2023/001/NLDAS_FORA0125_H.A20230101.1700.002.grb_CBP.gtiff

Band 10 Block=75x1 Type=Float64, ColorInterp=Undefined
  DescriEMENT=APCP
    GRIB_FORECAST_SECONDS=3600 sec
    GRIB_REFption = 0[-] SFC (Ground or water surface)
  NoData Value=9999
  Metadata:
    GRIB_COMMENT=Total precipitation [kg/m^2]
    GRIB_ELEMENT=APCP
    GRIB_FORECAST_SECONDS=3600 sec
    GRIB_REF_TIME=1672588800 sec UTC
    GRIB_SHORT_NAME=0-SFC
    GRIB_UNIT=[kg/m^2]
    GRIB_VALID_TIME=1672592400 sec UTC

[rburghol]

@COBrogan

• Set metadata date on the geotiff when creating it

gdal_edit.py /backup/meteorology/2021/001/NLDAS_FORA0125_H.A20210101.2000.002.grb_CBP.gtiff -mo "TIFFTAG_DATETIME=2021-01-01T20:00:00Z"`

• read metadata as a variable:

eval `gdalinfo /backup/meteorology/2021/001/NLDAS_FORA0125_H.A20210101.2000.002.grb_CBP.gtiff |grep TIFFTAG_DATETIME`
echo $TIFFTAG_DATETIME
2021-01-01T20:00:00Z